Previous date according filter

[Anonymous]

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

[Ashish_Mathur · Accepted 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]))

[Anonymous]

Thanks so much Ashish!

[Ashish_Mathur]

You are welcome.