Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Measure not doing anything in filter

Hi,

 

I have a dataset which looks as follows

MilestoneMilestoneTypeMilestoneDateRaw
Gate 2 reachedForecastDate26/04/2017
Gate 3 reachedForecastDate17/09/2017
Gate 4 reachedForecastDate14/11/2018
Gate 5 reachedForecastDate14/11/2019
Gate 2 reachedActualDate15/05/2017
Gate 3 reachedActualDate14/11/2017
Gate 4 reachedActualDate 
Gate 5 reachedActualDate 

 

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!

  • Anonymous's avatar
    Anonymous
    8 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi,

     

    Is anyone able to help with this?

     

    Many Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable

    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