Forum Discussion

ganchevd's avatar
ganchevd
Icon for Helper I rankHelper I
9 years ago
Solved

Calculate days overdue in Edit Query mode MS Power BI

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.

  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    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

4 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    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

  • fhill's avatar
    fhill
    Icon for Resident Rockstar rankResident Rockstar
    9 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    This literally didn't work.