Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Robyrubyjane
Frequent Visitor

Date Overdue

Robyrubyjane_0-1675650996423.png

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

1 ACCEPTED SOLUTION
andrewpirie
Resolver II
Resolver II

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"
, "-"
)
andrewpirie_1-1675655132220.png

 

 

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 

View solution in original post

2 REPLIES 2
Robyrubyjane
Frequent Visitor

omg, it worked 😭 Thank you so much

andrewpirie
Resolver II
Resolver II

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"
, "-"
)
andrewpirie_1-1675655132220.png

 

 

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 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.