5

I need to find the month difference between two dates (checkIn and Checkout dates) in Power Query (M-query). It can be similar to DAX bellow.

period of months = DATEDIFF([dateCheckIn], [dateCheckOut], MONTH )

I found the function daysDiff = each Duration.days([date1]-[date2]) but there is no function for month difference.

ZygD
  • 22,092
  • 39
  • 79
  • 102
Thao N
  • 81
  • 1
  • 2
  • 8
  • 5
    I think I got it. ((Date.Year([Change_Close_Date])-Date.Year([Change_Create_Date]))*12) + Date.Month([Change_Close_Date]) - Date.Month([Change_Create_Date]) – Thao N Mar 12 '20 at 19:18

5 Answers5

6

As a new user to Power BI I am finding the need to filter between DAX and Power Query answers to be tiresome! DAX has a DATEDIFF function and Power Query (the M language?) doesn't? Why not?

There is a Duration function in M.

But that doesn't do months.

So I am grateful to you Thao N for asking and answering this question - a very neat line of code!

This is what you need:

((Date.Year([Change_Close_Date])-Date.Year([Change_Create_Date]))*12) + Date.Month([Change_Close_Date]) - Date.Month([Change_Create_Date])
Dima Kozhevin
  • 3,602
  • 9
  • 39
  • 52
JimmyWeb
  • 73
  • 1
  • 2
  • 6
1

I use this (I calculate with half months). For full months use 30 and 1, for quarter months use 7.5 and 4.

Number.IntegerDivide(Duration.Days([End]-[Start]), 15)/2)
Mario LOBO
  • 41
  • 6
0

You can use this formula:

Date.Month(Date1)-Date.Month(Date2)+12*(Date.Year(Date1)-Date.Year(Date2))
Adrian Mole
  • 49,934
  • 160
  • 51
  • 83
Mina Ghazawy
  • 1
0

I thought this help me so if you like:

(12-Date.Month([StartDate])+1)+
(Date.Month([EndDate]))+
(((Date.Year([EndDate])-Date.Year([StartDate]))-1)*12)
Chi1ink
  • 1
  • As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jul 12 '22 at 04:46
0

I think you could use Number.From(([Date1] - [Date2])/30).

Petter Friberg
  • 21,252
  • 9
  • 60
  • 109
DaccioZilla
  • 1