Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Previous date according filter

Hi,

 

I have a list of project milestones. Format is as follows:

 

ProjectID    Milestone             Milestone Date

1Milestone A2/09/2015
1Milestone B22/10/2015
1Milestone C11/12/2015
1Milestone D30/01/2016
1Milestone E20/03/2016
2Milestone A14/05/2016
2Milestone B3/07/2016
2Milestone C22/08/2016
2Milestone D11/10/2016
2Milestone E30/11/2016
3Milestone A25/11/2016
3Milestone B14/01/2017
3Milestone C5/03/2017
3Milestone D24/04/2017
3Milestone E13/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.

 

ProjectIDMilestoneMilestone DatePrevious Milestone
1Milestone A2/09/2015 
 Milestone B22/10/20152/09/2015
 Milestone C11/12/201522/10/2015
 Milestone D30/01/201611/12/2015
 Milestone E20/03/201630/01/2016
2Milestone A14/05/2016 
 Milestone B3/07/201614/05/2016
 Milestone C22/08/20163/07/2016
 Milestone D11/10/201622/08/2016
 Milestone E30/11/201611/10/2016
3Milestone A25/11/2016 
 Milestone B14/01/201725/11/2016
 Milestone C5/03/201714/01/2017
 Milestone D24/04/20175/03/2017
 Milestone E13/06/201724/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:

 

ProjectIDMilestoneMilestone DatePrevious Milestone
1Milestone A2/09/2015 
 Milestone C11/12/20152/09/2015
 Milestone E20/03/201611/12/2015
2Milestone A14/05/2016 
 Milestone C22/08/201614/05/2016
 Milestone E30/11/201622/08/2016
3Milestone A25/11/2016 
 Milestone C5/03/201725/11/2016
 Milestone E13/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

  • 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]))