Compare with dates

[Apple08]

Hi All

Please could I seek your advice: I need a DAX ‘Status’ to compare dates below:

If Measure_Actual_Date = Blank then shows as ‘No Actual’

If Measure_Actual_Date is not blank then to compare with Measure_Schedule_Date:

• Measure_Schedule_Date > Measure_Actual_Date then ‘Early’
• Measure_Schedule_Date=Mearsure_Actual_Date then ‘On time’
• Measure_Schedule_Date<Mearsure_Actual_Date then ‘Late’

I need another ‘Late Status’ to show:

If the above ‘Status’ is late, then count the number of days between Measure_Schedule_Date and Measure_Actual_Date:

Count number of late items less than 8 days between the date difference above

Count number of late items 8 days or more between the date difference above

Any help would be appreciated.

[Apple08]

Fantastic!  It works perfectly, many thanks Adamboer.

By the way, I am now using columns rather than measures,  I wonder are you able to help how can I get the counting for the statues such as "Late", "Early" etc?  Many thanks.

[Adamboer · Accepted solution]

Certainly, I can provide you with a DAX formula to achieve the desired results. Here is an example formula you can use to create a calculated column for the "Status":

Status =
IF(ISBLANK([Measure_Actual_Date]), "No Actual",
IF([Measure_Schedule_Date] > [Measure_Actual_Date], "Early",
IF([Measure_Schedule_Date] = [Measure_Actual_Date], "On time",
"Late"
)
)
)

To create the "Late Status" measure, you can use the following formula:
Late Status =
IF([Status] = "Late",
IF(DATEDIFF([Measure_Schedule_Date], [Measure_Actual_Date], DAY) < 8,
"Less than 8 days late",
"8 or more days late"
),
BLANK()
)

This formula checks if the "Status" column is "Late", and then calculates the number of days between the "Measure_Schedule_Date" and "Measure_Actual_Date". If the number of days is less than 8, the formula returns "Less than 8 days late", otherwise it returns "8 or more days late". If the "Status" column is not "Late", the formula returns blank.

I hope this helps! Let me know if you have any further questions or need additional assistance.