Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Cumulative total (last week)

Hello  -  Currently I have this measure which gives me the YTD cumulative total that I need, by week.   However, I would like to have another measure that gives me the cumulative total, but for the prior calendar week.   Just not sure how to modify this measure.    And just to confirm, I'm not trying to look 7 days back from today, and I am not looking for each "week's" cumulative total.  I am looking for the YTD total up to each week prior to the current week.     Any suggestions?  And thanks for any help!

 

Cumulative RMA Count =
VAR CurrentDate= MAX( 'Date Table'[Date])
RETURN CALCULATE([Distinct Count of RMAs], ALLEXCEPT('Date Table','Date Table'[Date]), 'Date Table'[Date]<=CurrentDate)
 
So, for example:   
 
Week Number     Cumulative YTD Total      Prior Calend Week YTD Total  (this is the column I need the new measure for)
Week 30                  32                                 25   (would be cumulative total as of week 29)
Week 31                  88                                 32  (cumulative total of all the prior weeks)
Week 32                  215                               88  (cumulative total of all the prior weeks)
Week 33                  250                               215  (cumulative total of all the prior weeks)
Week 34                  279                               250 (cumulative total of all the prior weeks)
 etc......
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hello sevenhills    I had a chance to play around with the formula above and was able to make minor tweaks to get what I needed and correct the issue that was happening with your measure.    The issue was that the very last cumulative date in the previous week column was matching with the actual previous week's total as I mentioned in a previous post.    I've changed it to this, which now captures my last row correctly according to what I need.   Thanks for getting me on the right path!   Will mark your answer as a solution  (with minor tweaks) ğŸ˜€

     

    Prev Cumulative Week RMA Count =
    VAR CurrentWeek = MAX('Date Table'[WeekEnding])
    RETURN CALCULATE([Distinct Count of RMAs], ALLEXCEPT('Date Table','Date Table'[WeekEnding]),'Date Table'[WeekEnding]<= (CurrentWeek - 1) )


7 Replies

  • sevenhills's avatar
    sevenhills
    Icon for Super User rankSuper User
    5 years ago

    Just curious, wondering whether you tried using "-7"

     

    Prev Cumulative RMA Count =
    VAR CurrentDate= MAX( 'Date Table'[Date])
    RETURN CALCULATE([Distinct Count of RMAs], ALLEXCEPT('Date Table','Date Table'[Date]), 'Date Table'[Date]<= (CurrentDate - 7) )

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hello sevenhills    I had a chance to play around with the formula above and was able to make minor tweaks to get what I needed and correct the issue that was happening with your measure.    The issue was that the very last cumulative date in the previous week column was matching with the actual previous week's total as I mentioned in a previous post.    I've changed it to this, which now captures my last row correctly according to what I need.   Thanks for getting me on the right path!   Will mark your answer as a solution  (with minor tweaks) ğŸ˜€

       

      Prev Cumulative Week RMA Count =
      VAR CurrentWeek = MAX('Date Table'[WeekEnding])
      RETURN CALCULATE([Distinct Count of RMAs], ALLEXCEPT('Date Table','Date Table'[WeekEnding]),'Date Table'[WeekEnding]<= (CurrentWeek - 1) )


      • sevenhills's avatar
        sevenhills
        Icon for Super User rankSuper User
        5 years ago

        Thank you and glad I was able to provide the right direction.

         

        🙂 

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      sevenhills    Your measure works for all of the prior weeks except the most recent one.  I think because it is looking 7 days back from today, instead of grabbing the full previous calendar week.  In other words, the last row below should show 2,132 in the 3rd column, and 17 for the difference (last column).   This would give me the full week of last week.   So, very close to what I need, but not quite exactly.  In the table I'd like to show the cumulative total as of this week, 2nd column, and then the full total (as of the end of the last calendar week, 3rd column).     Ideally, I would also use those values in a card visual as well.  For example, to show this week,  last week, and create a measure to show the difference.