Forum Discussion

CaveOfWonders's avatar
CaveOfWonders
Helper IV
4 years ago
Solved

Date 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 ) ...
Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    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

BA_Pete's avatar
BA_Pete
Super User
4 years ago

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's avatar
CaveOfWonders
Helper IV
4 years ago

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.