Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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])
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
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.
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]) )
Hi Owen,
Thanks for your previous post. The formula didn't work.
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])
Hi Owen,
It works!!! Thanks.
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
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.
User | Count |
---|---|
92 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |