Forum Discussion
Helper IIICumulative sum on summarize table
Memorable MemberHello 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 Result
Please let me know if you were looking for something different, Jeppe! 😄
SolleHi 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,
Solle- Solle
Hi 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,- Wilson_
Hi Solle,
Can you help clarify your request? If you want one cumulative total amount that ignores the date slicer, are you not just looking for total sales? If that's not what you're looking for, what number are you expecting this measure to return, based on the results of the original post's table?