Calculate days overdue in Edit Query mode MS Power BI

[ganchevd]

Hello All,

I have the following issue. I try to insert a custom column i Edit Query mode ow my report where to calculate the days overdue according to today. I tried the following: DateTime.Date(DateTime.LocalNow())-[Due Date], but then the result was 24 days. This is not my mission, because I have overdues from 1st August 2016, which I get as 24 day overdue and it is not correct. What formula should I use. It is easy for me to do it in query mode with DATEDIFF but this is not what I want.

Thank you all in advance.

[Anonymous]

This literally didn't work.

[v-huizhn-msft · Accepted solution]

Hi @ganchevd,

You can use Duration functions, please use the following M formula, which will give you the "Datediff" in days. But you can also have other datepart types as well.

=Duration.Days(Duration.From(DateTime.LocalNow()-[Date]))

Please see the expected result using my sample table date.



Best Regards,
Angelia

[ganchevd]

Works perfectly! Thank you

[fhill]

Please post some sample data so we can assist in more detail.  Thank You