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.
Hi,
I have a list of project milestones. Format is as follows:
ProjectID Milestone Milestone Date
1 | Milestone A | 2/09/2015 |
1 | Milestone B | 22/10/2015 |
1 | Milestone C | 11/12/2015 |
1 | Milestone D | 30/01/2016 |
1 | Milestone E | 20/03/2016 |
2 | Milestone A | 14/05/2016 |
2 | Milestone B | 3/07/2016 |
2 | Milestone C | 22/08/2016 |
2 | Milestone D | 11/10/2016 |
2 | Milestone E | 30/11/2016 |
3 | Milestone A | 25/11/2016 |
3 | Milestone B | 14/01/2017 |
3 | Milestone C | 5/03/2017 |
3 | Milestone D | 24/04/2017 |
3 | Milestone E | 13/06/2017 |
I am wishing to create a measure which calculates the previous milestone date for a project. The desired result would be as follows.
ProjectID | Milestone | Milestone Date | Previous Milestone |
1 | Milestone A | 2/09/2015 | |
Milestone B | 22/10/2015 | 2/09/2015 | |
Milestone C | 11/12/2015 | 22/10/2015 | |
Milestone D | 30/01/2016 | 11/12/2015 | |
Milestone E | 20/03/2016 | 30/01/2016 | |
2 | Milestone A | 14/05/2016 | |
Milestone B | 3/07/2016 | 14/05/2016 | |
Milestone C | 22/08/2016 | 3/07/2016 | |
Milestone D | 11/10/2016 | 22/08/2016 | |
Milestone E | 30/11/2016 | 11/10/2016 | |
3 | Milestone A | 25/11/2016 | |
Milestone B | 14/01/2017 | 25/11/2016 | |
Milestone C | 5/03/2017 | 14/01/2017 | |
Milestone D | 24/04/2017 | 5/03/2017 | |
Milestone E | 13/06/2017 | 24/04/2017 |
However I would like this measure to calcuate the previous milestone based on what is filtered. So for example if only A, C and E are filtered the result would be as follows:
ProjectID | Milestone | Milestone Date | Previous Milestone |
1 | Milestone A | 2/09/2015 | |
Milestone C | 11/12/2015 | 2/09/2015 | |
Milestone E | 20/03/2016 | 11/12/2015 | |
2 | Milestone A | 14/05/2016 | |
Milestone C | 22/08/2016 | 14/05/2016 | |
Milestone E | 30/11/2016 | 22/08/2016 | |
3 | Milestone A | 25/11/2016 | |
Milestone C | 5/03/2017 | 25/11/2016 | |
Milestone E | 13/06/2017 | 5/03/2017 |
The below creates what I am looking for in the first example but I am stumpted as to what to do for the second.
PreviousMilestoneDate = VAR MilestoneDate = MAX(Fact_Milestones[MilestoneDate]) VAR ProjectID = MAX(Fact_Milestones[ProjectID]) RETURN MAXX( FILTER( ALL(Fact_Milestones), Fact_Milestones[MilestoneDate] < MilestoneDate && Fact_Milestones[ProjectID] = ProjectID ), Fact_Milestones[MilestoneDate] )
Many Thanks
Solved! Go to Solution.
Hi,
I used these two measures
Date of Milestone = max(Table1[Milestone Date])
Previous milestone date = CALCULATE(MAX(Table1[Milestone Date]),DATESBETWEEN(Table1[Milestone Date],MINX(ALL(Table1[Milestone]),[Date of milestone]),MIN(Table1[Milestone Date])-1),ALLSELECTED(Table1[Milestone]))
Hi,
I used these two measures
Date of Milestone = max(Table1[Milestone Date])
Previous milestone date = CALCULATE(MAX(Table1[Milestone Date]),DATESBETWEEN(Table1[Milestone Date],MINX(ALL(Table1[Milestone]),[Date of milestone]),MIN(Table1[Milestone Date])-1),ALLSELECTED(Table1[Milestone]))
Thanks so much Ashish!
You are welcome.
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |