Forum Discussion
Helper IVDate Value must contain the Date Component - DAX to Power Query:
Hi All,
I have a DAX query I'm trying to shift to Power Query. Below is the DAX which is working.
New Column =
IF (
'Base_Table'[Opening_Date] < 'Base_Table'[Official_Date],
( -1 )
* MONTH (
ENDOFMONTH ( 'Base_Table'[Opening_Date] )
- ENDOFMONTH ( 'Base_Table'[Official_Date] )
),
MONTH (
ENDOFMONTH ( 'Base_Table'[Opening_Date] )
- ENDOFMONTH ( 'Base_Table'[Official_Date] )
)
)
However, I'm getting this error in Power Query:
Any ideas what I need to change in my PQ code below in order to get this working? Columns formatted as 'Date'
New_Column
= Table.AddColumn(
Previous_Step,
"New Column",
each
if [Opening_Date] < [Official_Date] then
(- 1)
* Date.Month(
Duration.Days(
Date.EndOfMonth([Opening_Date]) - Date.EndOfMonth([Official_Date])
)
)
else
Date.Month(
Duration.Days(Date.EndOfMonth([Opening_Date]) - Date.EndOfMonth([Official_Date]))
),
Int64.Type
)
Much Appreciated
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
8 Replies
- BA_Pete
Super User
Hi CaveOfWonders ,
Your errors are here:
You are trying to get the month number (Date.Month) from a number of days (Duration.Days), rather than from a date value.
To be honest, I can't see how even your DAX version is working as it seems as though you make the same mistake there ( MONTH(Duration) ).
Pete
- CaveOfWonders
Thanks Pete, The DAX is working, please see below. To be honest this is something I inherited and assumed was logically sound:
- BA_Pete
I'm struggling to understand what "New Column" in your example is representing to be honest.
At first glance, it looks like it's the number of months between [Opening_Date] and [Official_Date], but [ID] #74 appears to disprove this, as there's more than six months between these values as they span two years.
Can you spell out in layman's terms what this new column is supposed to return please?
Pete
- CaveOfWonders
I think I'm going to have to go back to the drawing board on this one. Suprised it works in DAX and not PQ though. I need to go back to those I inherited this from to try and understand what the intention behind the new column is and get back to you. I was blindly replicating without investigating until this happend.
- CaveOfWonders
It turns out that a difference in months is all that was required....🤦♂️
Why isn't there a datediff in Power Query?
- BA_Pete
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
