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() )
What happens if you add a conditional? Like..
IF( DATEDIFF( 'Calendar'[Week Ending] , TODAY() , DAY ) >= -6 , [YOUR MEASURE] , BLANK() )
Like this ? :
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]) && 'vw_Forecast_WeeklyRetrieved_Optum_DEV'[WeekEnding] <= MAX('vw_Forecast_WeeklyRetrieved_Optum_DEV'[TollGateDate]))
), BLANK())
Its giving me an error
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() )