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

     

OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper 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
    Icon for Super User rankSuper 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]) )

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi Owen,

       

      Thanks for your previous post.  The formula didn't work.

       

      Formula usedColumn 'Months.Months' shows number of months

      • OwenAuger's avatar
        OwenAuger
        Icon for Super User rankSuper User
        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])