cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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 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!

1 ACCEPTED SOLUTION
Solution Sage

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() )
8 REPLIES 8
Solution Sage

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?

Frequent Visitor

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.

Solution Sage

What happens if you add a conditional? Like..

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

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

Solution Sage

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.

Frequent Visitor

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.

Solution Sage

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

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors