DAX formula - on time status calculation

[PC2022]

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(

        1,

        LateTasks,

        msdyn_projecttask[Baseline Finish],

        ASC

    )

VAR FirstLateTask1 =

    TOPN(

        1,

        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()

    )

[techies · Accepted solution]

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(

        1,

        LateTasks,

        msdyn_projecttask[Baseline Finish],

        ASC

    )

VAR FirstLateStartTask =

    TOPN(

        1,

        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."

    )

[v-aatheeque]

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.

[maruthisp]

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

[PC2022]

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()

[v-aatheeque]

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.

[PC2022]

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

[gmsamborn]

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.

[PC2022]

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.