topic Re: Cumulative total for previous week does not work even though standard pattern is used in DAX Commands and Tips

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

KrisD — Fri, 14 Jan 2022 09:17:40 GMT

Hi!

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?

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

ValtteriN — Fri, 14 Jan 2022 09:35:10 GMT

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]<=EARLIER(Cumulativetotal[Date]))

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!

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

KrisD — Fri, 14 Jan 2022 09:44:17 GMT

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.

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

ValtteriN — Fri, 14 Jan 2022 10:01:39 GMT

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 -> so all the dates and then it performs SUM operation -> SUM of all. This is the cause for your error. Referring to the SQLBI's example: https://www.sqlbi.com/articles/computing-running-totals-in-dax/

Sales RT :=

VAR MaxDate = MAX ( 'Date'[Date] ) -- Saves the last visible date

RETURN

CALCULATE (

[Sales Amount], -- Computes sales amount

'Date'[Date] <= MaxDate, -- Where date is before the last visible date

ALL ( Date ) -- Removes any other filters from Date

)

The example syntax doesn't have FILTER because of this reason.

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

KrisD — Fri, 14 Jan 2022 10:11:55 GMT

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.
The following is the complete code where I check if the user wants to see current week, current month, previous week and previous month.

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

ValtteriN — Fri, 14 Jan 2022 10:52:30 GMT

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:

Slicer not applied:

Applied:

Dax:

Period RT =

VAR MaxDate = MAX ( 'Calendar'[Date] ) -- Saves the last visible date

RETURN

CALCULATE (

SUM(Cumulativetotal[Value]), --

'Calendar example'[Date] <= 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

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

KrisD — Mon, 17 Jan 2022 09:59:49 GMT

Thanks for your time, but I'm not sure I follow.

Your code only works in a pre-defined context, like the table previously posted where the dates are already set?

I need to virtually create the table with the min and max date and do the running total in that context.
However, I tried your code in the measure and below is the input + results.

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

Anonymous — Wed, 19 Jan 2022 09:00:50 GMT

Hi KrisD ,

According to your statement, I think All/Allselect function in code is correct ,if you want to calculate cumulative total.

Your measure logic looks correct. Sometimes it should look like as below.

cumulative total = calculate([Antal lastade ton],Filter(All(Table),[Date]<=Max([Date])))

However this measure should be based on [Antal lastade ton] as well. Could you share a sample without sensitive data with us and show us the result you want? This will make it easier for us to find the solution.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

VahidDM — Wed, 19 Jan 2022 23:06:16 GMT

Hi KrisD

Check this link:

https://www.vahiddm.com/post/weekly-time-intelligence-dax

Can you post sample data as text and expected output?
Not enough information to go on;

please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

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

KrisD — Thu, 27 Jan 2022 10:09:18 GMT

Hi, sorry for delayed response. I'm attaching a sample file link because I could not find the option to attach it here. File --> https://easyupload.io/1okeq9

I got the WTD to work, I'm having issues with Previous month instead, which is defined in the file.

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

KrisD — Thu, 27 Jan 2022 10:08:57 GMT

Hi, I've created a sample file with data, measures and desired result. --> https://easyupload.io/1okeq9

I got the WTD to work, I'm having issues with Previous month instead, which is defined in the file.