Cumulative sum on summarize table

[Solle]

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

[Wilson_]

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 Result

Please let me know if you were looking for something different, Jeppe! 😄

[Solle]

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,
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? 

[Solle]

Hi @Wilson_ ,

I am actually getting what I want but not just for the subtotals, which was the one I was focussing on, I didnt realize that the subtotal could sum to something different than the above rows. 

So I am achieving what I seek but for the totals/subtotals in my table I am getting some very weird values, which does not correspond to the rows above, do you know how to fix this?

Product Category	Sub Product Category	Cumulative Sales
1	1	927
1	2	7.953
1	3
1	4	245
1	5
1	6	1.216
1	Subtotal	7.953
2	Subtotal	792
3	Subtotal	2.761
Total		7.953

I have attached the results from my matrix where the correct subtotal for product category 1 should be 10.341.

Best Regards,
Solle

[Wilson_]

Hey Solle,

I think at this point, it might be easier for me if you could provide your pbix file (or a sanitized/smaller version) so I can look under the hood. 😄

[andhiii079845]

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

[Solle]

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