If statement to show text if most recent date is overdue

[Elisa112]

Hello Experts

I have a column of most recent dates and I want to create a column/measure to see whether the most recent date is 90+ days ago and if so returns the text "Overdue".

I have used the following dax but Im not getting the correct results

Overdue = IF('Table'[Most Recent Date] > 90, "Overdue", " ")

Overdue 2 = IF(IF('Table'[Most Recent Date] < 'Table'[Overdue], 0,1)=0,"Overdue", " ")

Here is the sample data

Table

Most Recent Date         Overdue Date

14/08/2024                    14/11/2024

04/10/2024                     04/01/2024

Expected Output

Table

Most Recent Date         Overdue Date          Overdue

14/08/2024                    14/11/2024             Overdue

04/10/2024                     04/01/2024

All help appreciated, thanks in advance

[DataNinja777]

Hi @Elisa112 ,

The issue with your DAX formula lies in the comparison logic for determining whether a date is 90 or more days old. To fix this, you can calculate the overdue status by comparing the "Most Recent Date" with the date 90 days prior to today. Use the following DAX formula for a calculated column:

Overdue = 
IF(
    'Table'[Most Recent Date] <= TODAY() - 90,
    "Overdue",
    BLANK()
)

This formula works by checking if the "Most Recent Date" is less than or equal to the date 90 days ago, calculated as TODAY() - 90. If the condition is true, it returns "Overdue"; otherwise, it returns blank.

For your example, if the "Most Recent Date" is 14/08/2024 and today's date is after 12/11/2024, it will return "Overdue." If the "Most Recent Date" is 04/10/2024 and today's date is before 02/01/2025, it will not return "Overdue." Ensure that the "Most Recent Date" column is correctly formatted as a date type to get accurate results. Let me know if you need further clarification or assistance.

Best regards,

[Kedar_Pande · Accepted solution]

@Elisa112 

Correct DAX for a Calculated Column:

Overdue = 
IF(
DATEDIFF('Table'[Most Recent Date], TODAY(), DAY) > 90,
"Overdue",
" "
)

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Elisa112]

Thanks very much, works great!