Date Overdue

Robyrubyjane — Mon, 06 Feb 2023 02:39:00 GMT

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

Re: Date Overdue

andrewpirie — Mon, 06 Feb 2023 03:54:08 GMT

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 that time zone

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

Re: Date Overdue

Robyrubyjane — Mon, 06 Feb 2023 04:29:10 GMT

omg, it worked 😭 Thank you so much

topic Re: Date Overdue in DAX Commands and Tips

Date Overdue

Re: Date Overdue

Re: Date Overdue