DAX formula - on time status calculation

Hi PC2022 

You didn't mention which error message you received.

Taking a quick look at your DAX I noticed that your SWITCH(TRUE()) usage is incorrect in the RETURN.

I think this is what you are trying to accomplish.

RETURN
    SWITCH(
		TRUE(),
        NOT ISBLANK(TaskName),
			TaskName1 & " is " & StartDaysLate & " days past the expected start date",
			TaskName & " is " & FinishDaysLate & " days past the expected completion date"
    )

Personally, I find this more readable.

RETURN
    IF(
        NOT ISBLANK(TaskName),
			TaskName1 & " is " & StartDaysLate & " days past the expected start date",
			TaskName & " is " & FinishDaysLate & " days past the expected completion date"
    )

I hope I understood your requirement correctly.

Let me know if you have any questions.

Hi PC2022,

As per my understanding you You’re trying to identify the first late milestone task by start or finish date and return a status string indicating which task is late and by how many days. Below is the DAX expression derived from original post:

FirstLateMilestoneTask =
VAR TodayDate = TODAY()
VAR LateTasks =
FILTER (
msdyn_projecttask,
msdyn_projecttask[Schedule Status] = "Off Schedule"
&& msdyn_projecttask[Required Tasks.Flag] = "y"
&& msdyn_projecttask[% Complete] < 1
&& NOT ( ISBLANK ( msdyn_projecttask[Baseline Finish] ) )
)
VAR FirstLateFinishTask =
TOPN (
1,
LateTasks,
msdyn_projecttask[Baseline Finish],
ASC
)
VAR FirstLateStartTask =
TOPN (
1,
LateTasks,
msdyn_projecttask[Baseline Start],
ASC
)
VAR TaskNameFinish =
MAXX ( FirstLateFinishTask, msdyn_projecttask[Project Task Name] )
VAR FinishDate =
MAXX ( FirstLateFinishTask, msdyn_projecttask[Baseline Finish] )
VAR FinishDaysLate =
TodayDate - FinishDate

VAR TaskNameStart =
MAXX ( FirstLateStartTask, msdyn_projecttask[Project Task Name] )
VAR StartDate =
MAXX ( FirstLateStartTask, msdyn_projecttask[Baseline Start] )
VAR StartDaysLate =
TodayDate - StartDate

// Which is more overdue: start or finish?
RETURN
SWITCH (
TRUE (),
// If any late start milestone found and is in the past
NOT ISBLANK ( TaskNameStart ) && StartDate < TodayDate,
TaskNameStart & " is " & StartDaysLate & " days past the expected start date",
// If any late finish milestone found and is in the past
NOT ISBLANK ( TaskNameFinish ) && FinishDate < TodayDate,
TaskNameFinish & " is " & FinishDaysLate & " days past the expected finish date",
// If neither, return blank
BLANK()
)

Above DAX returns as below

1. If there’s a late start milestone in the past: show it.
2. Else if there’s a late finish milestone in the past: show it.
3. Else: blank.

Please let me know if you have further questions.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X