Forum Discussion

Robyrubyjane's avatar
Robyrubyjane
Frequent Visitor
3 years ago
Solved

Date Overdue

hi I want to ask, so I have data like this rental start and rental end, I want the output in the SLA column if today's date is close to the rental end date then the output will be "near overdue", if today's date has reached the rental end then the output will be "overdue" but if today's date has passed from the rental end date maybe about a month then the output will be "flag overdue"

 

Thanks in Advance!!

Need Help
  • andrewpirie's avatar
    andrewpirie
    3 years ago

    Here's a PowerQuery custom field expression and DAX equivalent. Prefer the PowerQuery version for performance.

     

    As PowerQuery, note that I've assumed that I can generalize 1 month = 31 days:

     

    if Duration.Days(Duration.From([rental end]-DateTime.Date(DateTime.LocalNow()))) <= -31 then "flag overdue" else if Duration.Days(Duration.From([rental end]-DateTime.Date(DateTime.LocalNow()))) < 0 then "overdue" else if Duration.Days(Duration.From([rental end]-DateTime.Date(DateTime.LocalNow()))) <= 14 then "rental near overdue" else "-"

     

    Here's a DAX calculated column expression equivalent:

    status = SWITCH(
    TRUE()
    , DATEDIFF(TODAY(), Rentals[rental end], DAY) <= -31, "flag overdue"
    , DATEDIFF(TODAY(), Rentals[rental end], DAY) < 0, "overdue"
    , DATEDIFF(TODAY(), Rentals[rental end], DAY) < 14, "rental near overdue"
    , "-"
    )

     

     

    Note that the Power BI service runs DAX NOW() and TODAY() in UTC, and I believe it will with PowerQuery date expressions too, so be careful comparing things with the current date if being a day out is a problem. This issue won't be visible in Power BI Desktop, but will be visible on the published report.
     
     
    The following article describes the issue around relative date slicers, but the same issue occurs with comparisons to the current date. It also describes how to resolve the issue: https://radacad.com/relative-date-slicer-for-your-local-time-zone-in-power-bi 

2 Replies

  • andrewpirie's avatar
    andrewpirie
    Resolver II
    3 years ago

    Here's a PowerQuery custom field expression and DAX equivalent. Prefer the PowerQuery version for performance.

     

    As PowerQuery, note that I've assumed that I can generalize 1 month = 31 days:

     

    if Duration.Days(Duration.From([rental end]-DateTime.Date(DateTime.LocalNow()))) <= -31 then "flag overdue" else if Duration.Days(Duration.From([rental end]-DateTime.Date(DateTime.LocalNow()))) < 0 then "overdue" else if Duration.Days(Duration.From([rental end]-DateTime.Date(DateTime.LocalNow()))) <= 14 then "rental near overdue" else "-"

     

    Here's a DAX calculated column expression equivalent:

    status = SWITCH(
    TRUE()
    , DATEDIFF(TODAY(), Rentals[rental end], DAY) <= -31, "flag overdue"
    , DATEDIFF(TODAY(), Rentals[rental end], DAY) < 0, "overdue"
    , DATEDIFF(TODAY(), Rentals[rental end], DAY) < 14, "rental near overdue"
    , "-"
    )

     

     

    Note that the Power BI service runs DAX NOW() and TODAY() in UTC, and I believe it will with PowerQuery date expressions too, so be careful comparing things with the current date if being a day out is a problem. This issue won't be visible in Power BI Desktop, but will be visible on the published report.
     
     
    The following article describes the issue around relative date slicers, but the same issue occurs with comparisons to the current date. It also describes how to resolve the issue: https://radacad.com/relative-date-slicer-for-your-local-time-zone-in-power-bi