Date Overdue

[Robyrubyjane]

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!!

[Robyrubyjane]

omg, it worked 😭 Thank you so much

[andrewpirie · Accepted solution]

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.

Power BI service operates in the UTC time zone, and evaluates all locale based time functions in tha...

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