Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi
I have a dataset similar to the table below, the table contains two activities with one row per step that generate progress/percent complete. I would like to get a column with a "1" or bool true in to indicate what row has the newest ActualDate per activity, and one column with a "1" or bool true to indicate what is the next step within the activity (row with oldest planned date without actual date)
ActivityID | ActivityName | ActualDate | PlannedDate | PercentComplete |
A1000 | Activity 1 | 10.07.2017 | 10.07.2017 | 25 |
A1000 | Activity 1 | 16.09.2017 | 10.09.2017 | 50 |
A1000 | Activity 1 | 02.10.2017 | 60 | |
A1000 | Activity 1 | 28.10.2017 | 75 | |
A1000 | Activity 1 | 03.11.2017 | 100 | |
A2000 | Activity 2 | 03.10.2017 | 02.10.2017 | 10 |
A2000 | Activity 2 | 05.10.2017 | 06.10.2017 | 20 |
A2000 | Activity 2 | 12.10.2017 | 12.10.2017 | 50 |
A2000 | Activity 2 | 15.10.2017 | 60 | |
A2000 | Activity 2 | 20.10.2017 | 70 | |
A2000 | Activity 2 | 28.10.2017 | 100 |
The result should be something like this :
ActivityID | ActivityName | ActualDate | PlannedDate | PercentComplete | LastActual | NextPlanned |
A1000 | Activity 1 | 10.07.2017 | 10.07.2017 | 25 | ||
A1000 | Activity 1 | 16.09.2017 | 10.09.2017 | 50 | 1 | |
A1000 | Activity 1 | 02.10.2017 | 60 | 1 | ||
A1000 | Activity 1 | 28.10.2017 | 75 | |||
A1000 | Activity 1 | 03.11.2017 | 100 | |||
A2000 | Activity 2 | 03.10.2017 | 02.10.2017 | 10 | ||
A2000 | Activity 2 | 05.10.2017 | 06.10.2017 | 20 | ||
A2000 | Activity 2 | 12.10.2017 | 12.10.2017 | 50 | 1 | |
A2000 | Activity 2 | 15.10.2017 | 60 | 1 | ||
A2000 | Activity 2 | 20.10.2017 | 70 | |||
A2000 | Activity 2 | 28.10.2017 | 100 |
Can anyone help me out with regards to where I should start?
Solved! Go to Solution.
HI @dark_z
This is the revised Formula
LastActual= VAR MyMax = MAXX ( FILTER ( Table1, Table1[ActivityName] = EARLIER ( Table1[ActivityName] ) && NOT ( ISBLANK ( Table1[ActualDate] ) ) ), Table1[PercentComplete] ) RETURN IF ( Table1[ActualDate] = MAXX ( FILTER ( Table1, Table1[ActivityName] = EARLIER ( Table1[ActivityName] ) ), Table1[ActualDate] ) && Table1[PercentComplete] = MyMax, 1 )
Hi @dark_z
Add this calculated column for Lastactual
LastActual = IF ( Table1[ActualDate] = MAXX ( FILTER ( Table1, Table1[ActivityName] = EARLIER ( Table1[ActivityName] ) ), Table1[ActualDate] ), 1 )
Thanks a lot. Actually, that solved my initial problem!
However, when i look closer at the data, i found an issue using what I was trying to do as some of the activities has the same actual date on several rows and I need only the row with the highest Percent Complete. I have updated the table below to show how the result became. For "Activity 1" i only want a "1" in LastActual on the row with 50% complete, not the one with 25% complete. Are you able to help me out with that as well?
ActivityID | ActivityName | ActualDate | PlannedDate | PercentComplete | LastActual | NextPlanned |
A1000 | Activity 1 | 16.09.2017 | 10.07.2017 | 25 | 1 | |
A1000 | Activity 1 | 16.09.2017 | 10.09.2017 | 50 | 1 | |
A1000 | Activity 1 | 02.10.2017 | 60 | 1 | ||
A1000 | Activity 1 | 28.10.2017 | 75 | |||
A1000 | Activity 1 | 03.11.2017 | 100 | |||
A2000 | Activity 2 | 03.10.2017 | 02.10.2017 | 10 | ||
A2000 | Activity 2 | 12.10.2017 | 06.10.2017 | 20 | 1 | |
A2000 | Activity 2 | 12.10.2017 | 12.10.2017 | 50 | 1 | |
A2000 | Activity 2 | 15.10.2017 | 60 | 1 | ||
A2000 | Activity 2 | 20.10.2017 | 70 | |||
A2000 | Activity 2 | 28.10.2017 | 100 |
HI @dark_z
This is the revised Formula
LastActual= VAR MyMax = MAXX ( FILTER ( Table1, Table1[ActivityName] = EARLIER ( Table1[ActivityName] ) && NOT ( ISBLANK ( Table1[ActualDate] ) ) ), Table1[PercentComplete] ) RETURN IF ( Table1[ActualDate] = MAXX ( FILTER ( Table1, Table1[ActivityName] = EARLIER ( Table1[ActivityName] ) ), Table1[ActualDate] ) && Table1[PercentComplete] = MyMax, 1 )
Hi @dark_z
Following calculated column for Next Planned
NextPlanned = IF ( Table1[PlannedDate] = MINX ( FILTER ( Table1, Table1[ActivityName] = EARLIER ( Table1[ActivityName] ) && ISBLANK ( Table1[ActualDate] ) ), Table1[PlannedDate] ), 1 )
User | Count |
---|---|
96 | |
87 | |
78 | |
72 | |
68 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |