Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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,
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
Hi,
Is anyone able to help with this?
Many Thanks
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |