Return a specific value in column when meeting a criteria

[PC2022]

I need to write DAX to show which milestone task (highlighted) is causing the schedule to be late. For example, the task is "100% PPT Review of Complete Package" is behind schedule causing the entire project to be late 

I need the output to say "100% PPT Review of Complete Package" is 91 days (calculated from today) late.

This is my DAX to calculate project schedule status. 

Schedule Status =

    VAR CurrentTaskOffScheduleCount = CALCULATE([Count of Off Schedule Tasks], msdyn_projecttask[Is Next Task] = TRUE())

    VAR CurrentTaskAtRiskCount = CALCULATE([Count of At Risk Tasks], msdyn_projecttask[Is Next Task] = TRUE())

    VAR ProjScheduleStatus = SWITCH(TRUE(),

                                        msdyn_project[Baseline Start] = BLANK() && msdyn_project[Baseline Finish] = BLANK(), "",

                                        CurrentTaskOffScheduleCount > 0, "Off Schedule",

                                        CurrentTaskAtRiskCount > 0, "On Watch",

                                        "On Schedule")

    RETURN ProjScheduleStatus

I was trying DAX below but it brings up ALL late tasks. I need to show only the first milestone task that is late.

OnTimeStatus = IF(msdyn_projecttask[Schedule Status] = "Off Schedule", msdyn_projecttask[Project Task Name])," - ")

[Anonymous]

Hi @PC2022 ,
Thank you for reaching out to the Microsoft fabric community forum.

I wanted to check if you had the opportunity to review the information provided by @DataNinja777 . Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[DataNinja777 · Accepted solution]

Hi @PC2022 ,

To return the name of the first milestone task that is currently late and calculate how many days it’s overdue, you can use the following DAX measure. This measure filters for tasks that are marked as "Off Schedule", not 100% complete, and have a valid finish date. It then finds the task with the earliest finish date among those, calculates how late it is compared to today’s date, and returns the task name along with the number of days late.

FirstLateMilestoneTask =
VAR TodayDate = TODAY()
VAR LateTasks =
    FILTER(
        msdyn_projecttask,
        msdyn_projecttask[Schedule Status] = "Off Schedule"
            && msdyn_projecttask[% Complete] < 100
            && NOT(ISBLANK(msdyn_projecttask[Finish Date]))
    )
VAR FirstLateTask =
    TOPN(
        1,
        LateTasks,
        msdyn_projecttask[Finish Date],
        ASC
    )
VAR TaskName =
    MAXX(FirstLateTask, msdyn_projecttask[Project Task Name])
VAR FinishDate =
    MAXX(FirstLateTask, msdyn_projecttask[Finish Date])
VAR DaysLate =
    DATEDIFF(FinishDate, TodayDate, DAY)
RETURN
    IF(
        NOT ISBLANK(TaskName),
        TaskName & " is " & DaysLate & " days late",
        BLANK()
    )

This returns only one result: the name of the first late milestone task and how many days late it is.

Best regards,

[PC2022]

Thank you so much! It worked!