Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Cumulative Sum or Running Total

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.Data ModelMeasure Dax Code

Cumulative Sum
DAX Commands and Tips

3 Replies

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

    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])))

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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.