Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have two tables: Tasks and TaskBaselines
Tasks:
TaskId, TaskFinishDate
TaskBaselines: TaskId, TaskBaselineNumber, TaskBaselineFinishDate
I would like to get a DATEDIFF (in days) between the TaskFinishDate and the related TaskBaseline which has the highest TaskBaselineNumber (meaning it is the last baseline created).
I have tried meddling with it for some time but can't seem to find a way.
Link to some mockdata: https://drive.google.com/open?id=1FCQpVKCAmMa8NnC_kB2P--qknxzca_lT
Solved! Go to Solution.
Hi @tuomas-i,
Please create below measures:
highest baseline number = CALCULATE ( MAX ( TaskBaselines[BaselineNumber] ), ALLEXCEPT ( TaskBaselines, TaskBaselines[TaskId] ) ) Latestfinishtime = CALCULATE ( MAX ( TaskBaselines[TaskBaselineFinishDate] ), FILTER ( ALL ( TaskBaselines ), TaskBaselines[BaselineNumber] = [highest baseline number] && TaskBaselines[TaskId] = SELECTEDVALUE ( Tasks[TaskId] ) ) ) datediff = DATEDIFF ( SELECTEDVALUE ( Tasks[TaskFinishDate] ), [Latestfinishtime], DAY )
Best regards,
Yuliana Gu
Hi @tuomas-i,
Please create below measures:
highest baseline number = CALCULATE ( MAX ( TaskBaselines[BaselineNumber] ), ALLEXCEPT ( TaskBaselines, TaskBaselines[TaskId] ) ) Latestfinishtime = CALCULATE ( MAX ( TaskBaselines[TaskBaselineFinishDate] ), FILTER ( ALL ( TaskBaselines ), TaskBaselines[BaselineNumber] = [highest baseline number] && TaskBaselines[TaskId] = SELECTEDVALUE ( Tasks[TaskId] ) ) ) datediff = DATEDIFF ( SELECTEDVALUE ( Tasks[TaskFinishDate] ), [Latestfinishtime], DAY )
Best regards,
Yuliana Gu
Hi @tuomas-i
Please share some data on google drive or one-drive with the link posted here. Also what is the exact output you desire.
Cheers
CheenuSing
Hi @CheenuSing,
Added a mockdata link to the opening post.
So I have projects, and I want to see how each project's TaskFinishDate compares to last created (highest BaselineNumber) baseline's TaskBaselineFinishDate.
So basically, I want a table with the following format (only the last column is relevant though):
[ProjectName], [TaskName], [TaskFinishDate minus highest BaselineNumber TaskBaselineFinishDate (in days)]
Example Project, Example Task, -3
Thus, that example project's task is projected to finish 3 days ahead of schedule. Whether a measure or calculated column is used, does not matter (even though a measure might be more convenient since there are a lot of tasks and no point in calculating that for each one).
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |