Forum Discussion
Solved
Cumulative Total Measure - Going over current date
Hello, I am having a little issue with my Cumulative Total I created based on Weekending Date. I currently have a dataset that returns past totals based on weekending and also has future foreca...
Oh, sorry. You need to add a MAX:
Cumulative Total =IF( DATEDIFF( MAX( WE[Week Ending] ) , TODAY() , DAY ) >= -6 ,CALCULATE (SUM(WE[Val]),FILTER (ALL( WE[Week Ending]),WE[Week Ending] <= MAX ( WE[Week Ending]))) , BLANK() )
Well, now I'm confused. I didn't see this bit in the first DAX you posted:
&& 'vw_Forecast_WeeklyRetrieved_Optum_DEV'[WeekEnding] <= MAX('vw_Forecast_WeeklyRetrieved_Optum_DEV'[TollGateDate]))
Additionally, your error is referencing a date table and field I don't see being referenced in your measure at all.
I am really sorry about that, I copied the wrong measure I was playing around with. This is the one I have now and giving me the same error :
Cumulative Total 2 = IF(DATEDIFF('vw_Forecast_WeeklyRetrieved_Optum_DEV'[WeekEnding], TODAY(), DAY) >= -6,
CALCULATE (
SUM('vw_Forecast_WeeklyRetrieved_Optum_DEV'[Retrieved]),
FILTER (
ALL( 'vw_Forecast_WeeklyRetrieved_Optum_DEV'[WeekEnding]),
'vw_Forecast_WeeklyRetrieved_Optum_DEV'[WeekEnding] <= MAX ( 'vw_Forecast_WeeklyRetrieved_Optum_DEV'[WeekEnding])
)
), BLANK())
i really appreciate your time and help with this.
Oh, sorry. You need to add a MAX:
Cumulative Total =IF( DATEDIFF( MAX( WE[Week Ending] ) , TODAY() , DAY ) >= -6 ,CALCULATE (SUM(WE[Val]),FILTER (ALL( WE[Week Ending]),WE[Week Ending] <= MAX ( WE[Week Ending]))) , BLANK() )CoreyP I really appreciate so much your time and quick response with this.