Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

DATADIFF not recognised

Hi there,

 

I am not able to get the DATEDIFF formula to work - it is not recognised by PowerBI.  Not sure why.  Any help would be much appreciated.

 

Thanks.

Need Help
  • OwenAuger's avatar
    OwenAuger
    8 years ago

    Hi Anonymous

     

    Oh yep sure - the code from my previous post was intended to replace the entire step i.e. to be pasted in the Advanced Editor rather than the Custom Column dialog box.

     

    If you are entering code in the Custom Column dialog box, use this instead:

     

    let
   fnMonthIndex = each Date.Year(_)*12 + Date.Month(_),
   todayindex = fnMonthIndex( DateTime.Date(DateTime.FixedLocalNow()) )
in
   todayindex - fnMonthIndex([Modified])

     

8 Replies

  • quentin_vigne's avatar
    quentin_vigne
    Solution Sage
    8 years ago

    Hi Anonymous

     

    Did you try adding this new column in another way ? 

     

    1 - Right click on your table - New column

     

    2 - Add this formula : Changed Type3 = DATEDIFF([Modified];Today();DAY)

     

    3- Should work

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      I am not able to do (as per your instruction) in Power Query Editor.  Are you able to use this formula in Power Query Editor? Or should I use a different formula?  Thanks for helping.

  • OwenAuger's avatar
    OwenAuger
    Super User
    8 years ago

    Hi Anonymous

     

    Yes, as noted above, DATEDIFF doesn't exist in the M language.

     

    It looks like you wanted to calculate duration in days? If so either of these M patterns should work. They subtract [Modified] from the current date, and convert to a number of days:

    = Table.AddColumn(#"Changed Type3", "Months", each Number.From(DateTime.Date(DateTime.FixedLocalNow())-[Modified]) )

= Table.AddColumn(#"Changed Type3", "Months", each Duration.Days(DateTime.Date(DateTime.FixedLocalNow())-[Modified]) )

    In case you were wanting months, the only method I know of in M is to calculate an index Date.Year(Date)*12 + Date.Month(Date) for each date then subtract.

     

    Regards,

    Owen

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi Owen,

       

      Thanks for your assistance.  I managed to calculate the days.

       

      But, I don't get understand how to use the following to calculate in months:

       

       

      Where do I insert "Date.Year(Date)*12 + Date.Month(Date)" in the formula

      "= Table.AddColumn(#"Changed Type3", "Months", each Duration.Days(DateTime.Date(DateTime.FixedLocalNow())-[Modified]))"?

       

      Thanks.

      • OwenAuger's avatar
        OwenAuger
        Super User
        8 years ago

        Hi Anonymous

         

        Something like this is what I meant.

        I've actually put a function definition in here (fnMonthIndex) to convert any date to an index, to shorten the code a bit. 

        If you replace the whole "=Table.Addcolumn" step with this it should do the trick.

         

        = let
   fnMonthIndex = each Date.Year(_)*12 + Date.Month(_),
   todayindex = fnMonthIndex( DateTime.Date(DateTime.FixedLocalNow()) )
in
   Table.AddColumn(#"Changed Type3", "Months", each todayindex - fnMonthIndex([Modified]) )