Measure not doing anything in filter

[Anonymous]

Hi,

I have a dataset which looks as follows

Milestone	MilestoneType	MilestoneDateRaw
Gate 2 reached	ForecastDate	26/04/2017
Gate 3 reached	ForecastDate	17/09/2017
Gate 4 reached	ForecastDate	14/11/2018
Gate 5 reached	ForecastDate	14/11/2019
Gate 2 reached	ActualDate	15/05/2017
Gate 3 reached	ActualDate	14/11/2017
Gate 4 reached	ActualDate
Gate 5 reached	ActualDate

I am trying to create two measures, LastGate and NextGate.

LastGate is the most recent actual date and returns 14/11/2017

LastGate = 
CALCULATE(
	MAX(Fact_Milestones[MilestoneDateRaw]),
	Fact_Milestones[MilestoneType] = "ActualDate"
)

NextGate is the smallest forecast date which occurs after the last gate and looks as follows:

NextGate = 
CALCULATE(
	MIN(Fact_Milestones[MilestoneDateRaw]),
	Fact_Milestones[MilestoneType] = "ForecastDate",
	FILTER(
		ALL(Dim_Date[Date].[Date]),
		Dim_Date[Date].[Date] > [LastGate]
)
)

For some reason this returns 26/04/2017 which is just the smallest forecast date. Why does the LastGate measure not filter? Replacing it with DATE(2017,11,14) returns the desired result of 14/11/2018.

FILTER(
		ALL(Dim_Date[Date].[Date]),
		Dim_Date[Date].[Date] > DATE(2017,11,14)
)

Many thanks!

[v-yuezhe-msft · Accepted solution]

@Anonymous,

Please change your DAX to the following:

NextGate = var lastactualdate=[LastGate] 
return CALCULATE(
	MIN(Fact_Milestones[MilestoneDateRaw]),
	Fact_Milestones[MilestoneType] = "ForecastDate",
	FILTER(
		ALL(Dim_Date[Date]),
		Dim_Date[Date] > lastactualdate
)
)

Regards,
Lydia

[Anonymous]

Hi,

Is anyone able to help with this?

Many Thanks