topic Re: DAX formula - on time status calculation in DAX Commands and Tips

DAX formula - on time status calculation

PC2022 — Wed, 21 May 2025 21:04:22 GMT

My DAX below is to show which task is causing the project to be late, either due to start date being late or finish date being late. I get an error.

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 FirstLateTask =

TOPN(

LateTasks,

msdyn_projecttask[Baseline Finish],

ASC

)

VAR FirstLateTask1 =

TOPN(

LateTasks,

msdyn_projecttask[Baseline Start],

ASC

)

VAR TaskName =

MAXX(FirstLateTask, msdyn_projecttask[Project Task Name])

VAR TaskName1 =

MAXX(FirstLateTask1, msdyn_projecttask[Project Task Name])

VAR FinishDate =

MAXX(FirstLateTask, msdyn_projecttask[Baseline Finish])

VAR FinishDaysLate =

DATEDIFF(FinishDate, TodayDate, DAY)

VAR StartDate =

MAXX(FirstLateTask1, msdyn_projecttask[Project Task Name])

VAR StartDaysLate =

DATEDIFF(StartDate, TodayDate, DAY)

RETURN

SWITCH(TRUE(),

NOT ISBLANK(TaskName),

TaskName1 & " is " & StartDaysLate & " days past the expected start date", BLANK(),

TaskName & " is " & FinishDaysLate & " days past the expected completion date", BLANK()

)

Re: DAX formula - on time status calculation

gmsamborn — Tue, 27 May 2025 21:21:38 GMT

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.

Re: DAX formula - on time status calculation

maruthisp — Thu, 22 May 2025 01:22:36 GMT

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

If there’s a late start milestone in the past: show it.
Else if there’s a late finish milestone in the past: show it.
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

Re: DAX formula - on time status calculation

PC2022 — Thu, 22 May 2025 14:34:51 GMT

Thank you! This is what i get

Also, i just realized that my DAX needs to be fixed to compare BaselineFinish/Start to ActualFinish/Start, not TodayDate but it is not letting me add those particular columns.

Re: DAX formula - on time status calculation

PC2022 — Thu, 22 May 2025 14:37:42 GMT

Thank you! I used your suggestion but got an error.

RETURN

SWITCH (

TRUE (),

NOT ISBLANK ( FirstLateTask1 ) && StartDate < TodayDate,

FirstLateTask1 & " is " & StartDaysLate & " days past the expected start date",

NOT ISBLANK ( FirstLateTask ) && FinishDate < TodayDate,

FirstLateTask & " is " & FinishDaysLate & " days past the expected finish date",

BLANK()

Re: DAX formula - on time status calculation

v-aatheeque — Mon, 26 May 2025 18:31:41 GMT

Hi PC2022

You're encountering this error is happening because one of the fields you're using, like FirstLateTask1 or FirstLateTask, might be returning more than one value like a full row or column, instead of a single value.

In DAX, functions like SWITCH, comparisons (<), or combining text with & only work when you're dealing with a single value.

If the issue still persists ,Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Please show the expected outcome based on the sample data you provided.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.

Re: DAX formula - on time status calculation

PC2022 — Tue, 27 May 2025 19:54:09 GMT

Not working. I am trying to figure it out and will post once i do.

Re: DAX formula - on time status calculation

techies — Tue, 27 May 2025 20:10:23 GMT

Hi PC2022 please try this

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(

LateTasks,

msdyn_projecttask[Baseline Finish],

ASC

)

VAR FirstLateStartTask =

TOPN(

LateTasks,

msdyn_projecttask[Baseline Start],

ASC

)

VAR FinishTaskName =

MAXX(FirstLateFinishTask, msdyn_projecttask[Project Task Name])

VAR FinishDate =

MAXX(FirstLateFinishTask, msdyn_projecttask[Baseline Finish])

VAR FinishDaysLate =

DATEDIFF(FinishDate, TodayDate, DAY)

VAR StartTaskName =

MAXX(FirstLateStartTask, msdyn_projecttask[Project Task Name])

VAR StartDate =

MAXX(FirstLateStartTask, msdyn_projecttask[Baseline Start])

VAR StartDaysLate =

DATEDIFF(StartDate, TodayDate, DAY)

RETURN

SWITCH(

TRUE(),

StartDaysLate > 0,

StartTaskName & " is " & StartDaysLate & " days past the expected start date",

FinishDaysLate > 0,

FinishTaskName & " is " & FinishDaysLate & " days past the expected completion date",

"All required tasks are on track."

)

Re: DAX formula - on time status calculation

v-aatheeque — Thu, 29 May 2025 17:24:10 GMT

Hi PC2022,
we haven't heard back from you regarding our last response and wanted to check if your issue has been resolved.

If the response addressed by the community member for your query, please mark it as Accept Answer and click Yes if you found it helpful.
Thank You.