Cumulative Total of Measures by Date (+count totals of all previous years)

[brightcitrus]

Good Morning All,

I have been stuck on cumulative total formula for weeks now. I have done exhaustive research on this forum but none of the selected solutions worked for me. Any help or tips are greatly appreciated. Thank you in advance.

--------------------------------------------------------------------------------------------

I need to calculate a cumulative sum of products sold since 2003. My dataset only comprises 196 rows though. Each product has an order type which I filtered by first using the following formula:

 

Active Install Base = COUNTROWS(FILTER('JDE Install Base', 'JDE Install Base'[Shipped to Type] = "BG" || 'JDE Install Base'[Shipped to Type] = "C" ||'JDE Install Base'[Shipped to Type] = "CA" ||'JDE Install Base'[Shipped to Type] = "CI" ||'JDE Install Base'[Shipped to Type] = "CMQ" ||'JDE Install Base'[Shipped to Type] = "L" ||'JDE Install Base'[Shipped to Type] = "PAC" ||'JDE Install Base'[Shipped to Type] = "SI" ||'JDE Install Base'[Shipped to Type] = "SR" ||'JDE Install Base'[Shipped to Type] = "ST" ||'JDE Install Base'[Shipped to Type] = "TR" ||'JDE Install Base'[Shipped to Type] = "TS" || 'JDE Install Base'[Shipped to Type] = "TSS" || 'JDE Install Base'[Shipped to Type] = "W"))

Then I need to calculate the sum of this measure for each month since 2003 until today. I have used multiple formulas but none of them take into account the previous months' sold products. For example, for products sold in January 2019, I need the sum of all products sold since 2003 to January 2019.  Therefore, each month's sum of products sold needs to be greater than that of previous month.

 

I have also tried to compute the sum using dateadd using this formula, but it did not work.

Current Year = CALCULATE([Active Install Base])+CALCULATE([Active Install Base],DATEADD('Date'[Date],-1,year))+CALCULATE([Active Install Base],DATEADD('Date'[Date],-2,year))+CALCULATE([Active Install Base],DATEADD('Date'[Date],-3,year))+CALCULATE([Active Install Base],DATEADD('Date'[Date],-4,YEAR))+CALCULATE([Active Install Base],DATEADD('Date'[Date],-5,YEAR))+CALCULATE([Active Install Base],DATEADD('Date'[Date],-6,YEAR))+CALCULATE([Active Install Base],DATEADD('Date'[Date],-7,YEAR))+CALCULATE([Active Install Base],DATEADD('Date'[Date],-8,YEAR))+CALCULATE([Active Install Base],DATEADD('Date'[Date],-9,YEAR))+CALCULATE([Active Install Base],DATEADD('Date'[Date],-10,YEAR))+CALCULATE([Active Install Base],DATEADD('Date'[Date],-11,YEAR))+CALCULATE([Active Install Base],DATEADD('Date'[Date],-12,YEAR))+CALCULATE([Active Install Base],DATEADD('Date'[Date],-13,YEAR))+CALCULATE([Active Install Base],DATEADD('Date'[Date],-14,YEAR))+CALCULATE([Active Install Base],DATEADD('Date'[Date],-15,YEAR))+CALCULATE([Active Install Base],DATEADD('Date'[Date],-16,YEAR))

Currently, I am experimenting using this formula but does not seem to be working either.

me = SUMX(FILTER(ALLSELECTED('Date'[Date]), 'Date'[Date] <= TODAY()), 'JDE Install Base'[Active Install Base])

Thank you in advance!

[v-xuding-msft]

Hi @brightcitrus ,

Do you try that formula? Does it work? If you have solved the issue, please accept the helpful answers to solutions. If you solved by yourself, welcome to share to us. More people who encounter the same problem will benefit here.

Best Regards,

Xue Ding

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

[v-xuding-msft]

Hi @brightcitrus ,

The formula that Joren venema suggested should work. I add some blogs and video that you could learn from.

DAX for Power BI - Running Total (Cumulative Sum) 

Calculate Cumulative/Running Total In Power BI 

Computing running totals in DAX 

If the answers can't solve your problem, please share some sample data and your expected result. Then we will understand clearly and solve it quickly.

Best Regards,

Xue Ding

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

[Anonymous · Accepted solution]

What you are doing right now seems very unneccesary to me.

The following measure should do the trick.

Running Total MEASURE = 
CALCULATE (
     [Expression],
    FILTER (
        ALL ( 'table' ),
        'table'[Date] <= MAX ( 'Table'[Date] )
    )
)

It will evaluate all data in your data set that is smaller than the axis you use the visualize the data.

Kind regards
Joren Venema

Data & Analytics Consultant
If this reply solved your question be sure to mark this post as the solution to help others find the answer more easily.