Forum Discussion
Cumulative sum on summarize table
Hi All,
I have a summarize function from the below code:
VAR v1 =
SUMMARIZE(
FILTER(
FactSales,
FactSales[AsOfDate] = CALCULATE(MAX(FactSales[AsOfDate]), ALL(DimDate))
),
DimDate[Year],
"Last As Of Date", MAX(FactSales[AsOfDate]),
"Last Sales", LASTNONBLANK(FactSales[Sales], 0)
)Which provides this output
| Year | Last As Of Date | Last Sales |
| 2023 | 28-02-2023 00:00:00 | 1037467263,3053 |
| 2022 | 31-12-2022 00:00:00 | 2132113394,1337 |
| 2021 | 31-12-2021 00:00:00 | 2726595255,5036 |
| 2020 | 31-12-2020 00:00:00 | 4503000000 |
| 2019 | 31-12-2019 00:00:00 | 4372000000 |
| 2018 | 31-12-2018 00:00:00 | 3985000000 |
| 2017 | 31-12-2017 00:00:00 | 3681000000 |
| 2016 | 31-12-2016 00:00:00 | 3101000000 |
| 2015 | 31-12-2015 00:00:00 | 2607000000 |
| 2014 | 31-12-2014 00:00:00 | 2245000000 |
| 2013 | 31-12-2013 00:00:00 | 2144000000 |
| 2012 | 31-12-2012 00:00:00 | 1443300000 |
| 2011 | 31-12-2011 00:00:00 | 1470900000 |
| 2010 | 31-12-2010 00:00:00 | 3486451000 |
| 2009 | 31-12-2009 00:00:00 | 3520772000 |
What I then need is a cumulative sale based on the above table, but I cannot figure it out. I have tried using the SUMX function but it does not seem to work.
Can someone help me out on this?
Best Regards,
Solle
8 Replies
Hello Jeppe,
By cumulative sum, do you mean you would like 2009 to show 2009 sales, 2010 to show 2009-2010 sales, 2011 to show 2009-2011 sales, etc.?
If so, knowing nothing else about your model beyond this table (and assuming you would like this as a calculated column), you can try something like:
Cumulative Sales = -- This variable filters for only the rows that are before or equal to the date in the table row VAR FilteredTable = FILTER ( Table1, Table1[Last Date] <= EARLIER ( Table1[Last Date] ) ) -- This variable then takes only the filtered rows from the above table and sums up all the sales values VAR Result = SUMX ( FilteredTable, [Last Sales] ) RETURN ResultPlease let me know if you were looking for something different, Jeppe! 😄
Hi Wilson_
Thank you for taking your time to respond - I see that my code snippet from the summarize function didnt show properly, I have just updated my current summarize function, which I prefer to evolve into a cumulative DAX measure.
Yes I mean cumulative like you show above. Your code unfortunately does not apply as expected due to my summarize function I assume. Do you have any idea on how to fix the cumulative function?
Best Regards,
SolleHi Wilson_
I think your solution using SUMX correctly cumulates the data, but how can I avoid it from being affect from a date slicer, which is on my page? As what I want to accomplish is to always show the total no matter what dates are selected.VAR v1 = SUMMARIZE( FILTER( FactSales, FactSales[AsOfDate] = CALCULATE(MAX(FactSales[AsOfDate]), ALL(DimDate)) ), DimDate[Year], "Last As Of Date", MAX(FactSales[AsOfDate]), "Last Sales", LASTNONBLANK(FactSales[Sales], 0) ) VAR Result = SUMX(v1, [Last Sales] ) RETURN Result
Best Regards,
Try this:
Measure =
VAR _Currentyear = SELECTEDVALUE('sales'[Year])
RETURN IF(HASONEFILTER(sales[Year]),sumx(FILTER(ALLSELECTED('sales'),'sales'[Year]<=_Currentyear),'sales'[Last Sales]),sum(sales[Last Sales]))Hi andhiii079845
Thank you for taking your time to respond. I have jut tried your code, but as my summarize code didnt show properly, I am unfortunate that your code snippet does not work, do you have any better suggestion?
Best Regards,
Solle
