Cumulative Total Measure - Going over current date

[RSebastiani74]

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 forecast totals based on the same weekending date.  As you can see (squared in dashboard pic) the cumulative totals repeats for any future week endings.  I would like to add a condition where it only calculates the totals up to the closest weekending date to today (upcoming weekending).  

This is what i currently have as the Cumulative Measure : 

Cumulative Total =

CALCULATE (

SUM('vw_Forecast_WeeklyRetrieved'[Retrieved]),

FILTER (

ALL( 'vw_Forecast_WeeklyRetrieved'[WeekEnding]),

'vw_Forecast_WeeklyRetrieved'[WeekEnding] <= MAX ( 'vw_Forecast_WeeklyRetrieved'[WeekEnding])

)

)

My dataset looks like this. Cumulative Total is based out of the Retrieved column which is coming in my View. 

I want to not display any totals for any future dates for my cumulative total (Green Line).  Thank you very much!

[CoreyP]

If I'm understanding correctly, you just don't want to see any weekending dates on your x-axis ( and corresponding y values ) of your chart if those weekending dates are for weeks in the future? So the last weekending on your chart would be 9/30?

[RSebastiani74]

Hi There,

I dont want to see cumulative totals for any future dates but still want to see everything else (columns, weekendings)  As you can see the cumulative total repeats over and over for any future weekending dates which correspond to the forecast (columns) totals.

Thank you.

[CoreyP]

What happens if you add a conditional? Like..

IF( DATEDIFF( 'Calendar'[Week Ending] , TODAY() , DAY ) >= -6 , [YOUR MEASURE] , BLANK() )

[RSebastiani74]

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 

[CoreyP]

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. 

[RSebastiani74]

@CoreyP 

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.

[CoreyP · Accepted solution]

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() )

[RSebastiani74]

@CoreyP   I really appreciate so much your time and quick response with this.