Cumulative total for previous week does not work even though standard pattern is used

Question

Hi!&nbsp;I can't seem to get the rolling total to work, even though I (at least I think I do) have used the variations of cumulative patterns from SQL BI guys, Chandeep and Enterprise DNA. Obviously there's something I'm missing here!I get the date range as expected, but none of the patterns I have tried work. What's missing?&nbsp;

valtterin · Answer

Hej,For running total in a column the dax is a bit different. Try something like this:RT = CALCULATE(Sum(Cumulativetotal[Value]),ALL('Cumulativetotal'),Cumulativetotal[Date]&lt;=EARLIER(Cumulativetotal[Date]))&nbsp;I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

krisd · Answer

Hi and thanks for the swift reply!Actually it's not supposed to be calculated as a column in this context, I made the table in DAX Studio in order to troubleshoot. The calculation is intended for a visual as per below picture, where I either get the total for all the rows, or only on row by row context. But no cumulative I'm afraid.&nbsp;&nbsp;&nbsp;

valtterin · Answer

I see, in that case you could try removing the FILTER() in your CALCULATE. Now the FILTER filters a table where dates are smaller than the max date -&gt; so all the dates and then it performs SUM operation -&gt; SUM of all. This is the cause for your error. Referring to the SQLBI's example:&nbsp;https://www.sqlbi.com/articles/computing-running-totals-in-dax/Sales RT&nbsp;:=VAR&nbsp;MaxDate&nbsp;=&nbsp;MAX&nbsp;(&nbsp;'Date'[Date]&nbsp;)&nbsp;-- Saves the last visible dateRETURN&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[Sales Amount],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-- Computes sales amount&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Date'[Date]&nbsp;&lt;=&nbsp;MaxDate,&nbsp;&nbsp;-- Where date is before the last visible date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALL&nbsp;(&nbsp;Date&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-- Removes any other filters from Date&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;The example syntax doesn't have FILTER because of this reason.

krisd · Answer

Hi!I appreciate your input, thank you!The problem with your code is that do not have any external filters coming to the visual. The timeperiods are calculated by the use of SELECTEDVALUE.&nbsp;The following is the complete code where I check if the user wants to see current week, current month, previous week and previous month.&nbsp;

valtterin · Answer

Hi,If you are using slicer to select Period you can use ALLSELECTED. Here my slicer is linked to calendar and my imaginary period only includes values from January:&nbsp;Slicer not applied:Applied:Dax:Period RT = VAR MaxDate = MAX ( 'Calendar'[Date] ) -- Saves the last visible dateRETURNCALCULATE (SUM(Cumulativetotal[Value]), -- 'Calendar example'[Date] &lt;= MaxDate, ALLSELECTED( ( 'Calendar' ) ) )So you can either use ALLSELECTED and proceed with the SWITCH + TRUE or alternatively you can create link between calendar and your slicer table via calculated column

Forum Discussion

Cumulative total for previous week does not work even though standard pattern is used

10 Replies

Related content

Cumulative Total

Cumulative sales previous year in Stock running

Cumulative Sales for Full Previous Year

Creating Cumulative Graphs

Year to date cumulative