Forum Discussion

RSebastiani74's avatar
RSebastiani74
Frequent Visitor
2 years ago
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 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!

Need Help
  • CoreyP's avatar
    CoreyP
    2 years ago

    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

  • CoreyP's avatar
    CoreyP
    Solution Sage
    2 years ago

    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's avatar
      RSebastiani74
      Frequent Visitor
      2 years ago

      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's avatar
        CoreyP
        Solution Sage
        2 years ago

        What happens if you add a conditional? Like..

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