Forum Discussion
Previous date according filter
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
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]))
3 Replies
- Ashish_MathurSuper User
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]))
- AnonymousNot applicable
Thanks so much Ashish!
- Ashish_MathurSuper User
You are welcome.