How to use DATEDIFF in Power Query (M query) to look for months between two dates
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.
Answer
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])