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 foreca...
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() )
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() )
    • RSebastiani74's avatar
      RSebastiani74
      Frequent Visitor
      2 years ago

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

        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.