Forum Discussion
Anonymous
8 years agoNot applicable
Measure not doing anything in filter
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!
- Anonymous8 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
- AnonymousNot applicable
Hi,
Is anyone able to help with this?
Many Thanks
- AnonymousNot 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