'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.
Solution 1:[1]
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])
Solution 2:[2]
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)
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Dima Kozhevin |
| Solution 2 |