topic Re: Cumulative Sum or Running Total in DAX Commands and Tips

Cumulative Sum or Running Total

Anonymous — Fri, 16 Oct 2020 10:48:46 GMT

Hi community,

I have a little problem calculating a cumulative sum (or running total) via dax. As shown by my data model, I have two fact tables and a calendar table linked together via the "Date" column. The objective is to represent on the X axis the WD_in_the_month and for the whole of the various days, show the cumulative sum of the two amount values Fact_Delivery_note [Amount] and Fact_Invoince_Line [Amount] on the Y axis.
I enclose a screen with an example of the data available, bearing in mind that I have a two-year history and I do not have time filters in the dashboard and I have to represent the daily data for each month (e.g. I use start_date and date_today () to identify the reference time period as in the example from 1 to 07/07/2020).

I try this solution (attach script in dax) but it doesn't work.

Re: Cumulative Sum or Running Total

amitchandak — Fri, 16 Oct 2020 11:34:16 GMT

Anonymous , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

We can typically get Cumulative as measure like this

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(Sales[Sales Date])))

Re: Cumulative Sum or Running Total

Anonymous — Fri, 16 Oct 2020 12:29:39 GMT

Hi Amitchandak,

You can see the sample data and data model in the screen above.

Is everything clear or you need a more details?

Thanks in advance.

Fabio

Re: Cumulative Sum or Running Total

Anonymous — Mon, 19 Oct 2020 11:28:25 GMT

Hi Amit,

where do i upload a pbix file here?

I add further details to the problem you existed. Having available data covering several years, my goal is based on the time period of reference (eg. the reason why I insert in the code VAR DATE_TODAY ()) to identify the portion of the data of my interest (eg data that do from 2019 to 2020, I select only the current month October 2020 and i don't use a filter time period) and I calculate the cumulative sum of that period but the DAX code does not give me the desired results.

Attached I uploaded additional screens to make the problem clearer:

1) For each table in the yellow data model I have selected my period of interest (October 2020);
2) The DAX code and the result that comes from the graphs

Thanks a lot to anyone who can help me to solve this problem.