DATADIFF not recognised

[Anonymous]

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.

[OwenAuger]

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]

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]

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]

Hi Owen,

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

Formula usedColumn 'Months.Months' shows number of months

[OwenAuger · Accepted solution]

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

[Anonymous]

Hi Owen,

It works!!!  Thanks.

[quentin_vigne]

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]

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.