Skip to main content
cancel
Showing results for 
Search instead 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

Reply
RSebastiani74
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).  

 

 

 

RSebastiani74_0-1695861513936.png

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. 

RSebastiani74_1-1695861842283.png

 

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

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

View solution in original post

8 REPLIES 8
CoreyP
Solution Sage
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?

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.

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 
RSebastiani74_0-1695865293710.png

 

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. 

@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())
 
RSebastiani74_0-1695866838034.png

 

 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.  

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.