Date Value must contain the Date Component - DAX to Power Query:

[CaveOfWonders]

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

[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 · Accepted solution]

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

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

@CaveOfWonders ,

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.

[BA_Pete]

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