Forum Discussion
Helper IVDate Value must contain the Date Component - DAX to Power Query:
Hi CaveOfWonders ,
Wow! That's an...um...'creative' way of getting the number of months difference in DAX 😐
I believe PQ doesn't have things like DATEDIFF as that is a time intelligence calculation function. PQ isn't really designed for advanced calculations, hence the existence of DAX. I may be wrong, but there's plenty of examples of things you can do in DAX that don't exist in PQ for, I believe, the same reason.
For reference, if you want to just get the total months difference in PQ, it would be something like this:
Number.Abs( 12 * (Date.Year([Opening_Date]) - Date.Year([Official_Date])) + Date.Month([Opening_Date]) - Date.Month([Official_Date]) )I've used Number.Abs to swerve the whole issue of one date being earlier/later than the other.
This gives me the following output:
Pete
Helper IVIt turns out that a difference in months is all that was required....🤦♂️
Why isn't there a datediff in Power Query?
- BA_Pete
Super User
Hi CaveOfWonders ,
Wow! That's an...um...'creative' way of getting the number of months difference in DAX 😐
I believe PQ doesn't have things like DATEDIFF as that is a time intelligence calculation function. PQ isn't really designed for advanced calculations, hence the existence of DAX. I may be wrong, but there's plenty of examples of things you can do in DAX that don't exist in PQ for, I believe, the same reason.
For reference, if you want to just get the total months difference in PQ, it would be something like this:
Number.Abs( 12 * (Date.Year([Opening_Date]) - Date.Year([Official_Date])) + Date.Month([Opening_Date]) - Date.Month([Official_Date]) )I've used Number.Abs to swerve the whole issue of one date being earlier/later than the other.
This gives me the following output:
Pete
- CaveOfWonders
haha 😊, thanks for your patience Pete. Yes, I have no idea how someone came to that DAX solution....
Odd, I wonder why there are the some time intelligence functions in PQ such as difference in days, hours, mins, sec's etc... Either way your code worked perfectly, thank you.
CoW
- BA_Pete
Hi CaveOfWonders ,
Cool, glad it worked for you.
To my knowledge, there are no date/time difference functions in Power Query. You have duration functions, but these all require the explicit subraction of one date from another, and just convert the result into an apportioned duration. They also don't work across date borders e.g. I believe that Duration.Hours between 01/01/2021 10:00 and 31/01/2021 14:00 will just give you 4.
Anyway, I agree that there probably should be as it could easily fold to most types of SQL server, but there we are.
Pete