The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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.
Solved! Go to 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,
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.
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,
Thank you so much! It worked!
User | Count |
---|---|
28 | |
11 | |
8 | |
6 | |
5 |
User | Count |
---|---|
35 | |
14 | |
12 | |
9 | |
7 |