How to use DATEDIFF in Power Query (M query) to look for months between two dates

4
votes

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.

1
powerbipowerquerym
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

1 Answers

4
votes

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])