topic Re: Cumulative sales in DAX Commands and Tips

Cumulative sales

heetu24 — Tue, 13 Aug 2024 18:34:21 GMT

Hello, All I am stuck in one scenario.

Scenario: As per below screens shot. There is filter of Period and Year in Green color. In Power BI Period and Year are slicers.

In Year column records are : (TY:-This Year), (YA:-Year Ago), & (2YA: 2 Year ago)

In Period column records are : MAT, YTD & QTR.

I want to calculate Cumulative sales for "MAT" Period only and and each year such as TY, YA and 2 YA.

Note: API: Average Price Index column is given in the table. so against API cumulative sales value will be there.
Brand Name are same because imagine item level data so each brand represents different item you can ignore why it is same Brand in this scenario.

Above screen shot is in Excel. How to do in Power BI. I am beginner in DAX. Thank you to read my query 🙂

Re: Cumulative sales

Irwan — Wed, 14 Aug 2024 00:18:22 GMT

hello heetu24

please check if this accomodate your need.

since i dont see any order in that sample data, i assumed you have another column for sorting or indexing cummulative in that excel calculation. So i created another column for indexing from power query (this should be adjusted to your order column).

create a new measure with following DAX

Cummulative 1 = 
var _Index = SELECTEDVALUE('Table'[Index])
var _Sum =
CALCULATE(
    SUM('Table'[Sales Value]),
    FILTER(
        ALL('Table'),
        'Table'[Index]<=_Index
    )
)
Return
IF(
    _Index=1,
    0,
    _Sum
)

Cummulative 2 = 
var _Index = SELECTEDVALUE('Table'[Index])
var _Sum = 
SUMX(
    FILTER(
        ALL('Table'),
        'Table'[Index]<=_Index
    ),
    'Table'[Sales Value]
)
Return
IF(
    _Index=1,
    0,
    _Sum
)

both measure Cummulative 1 and Cummulative 2 have same result, but Cummulative 1 is using CALCULATE while Cummulative 2 is using SUMX. Choose either way should be fine.

Hope this will help you.

Thank you.

Re: Cumulative sales

heetu24 — Thu, 15 Aug 2024 07:29:18 GMT

Hi Thank you for this solution.

As I mentioned in screen shot. I am looking cumulative for year wise that you provided cumulative for TY and same for YA and 2 YA .

So if user select year select on TY/YA /2YA any of this so dynamically it can change cumulative sales. Is it possible to use summarize DAX function?

Re: Cumulative sales

Anonymous — Fri, 16 Aug 2024 05:21:53 GMT

Hi All
Fisrtly Irwan thank you for your solution!
And heetu24, We just need to make a slight change in Irwan's DAX, and the filtering of YEARS with the selectedvalue function can be done to accumulate different years.

Measure = VAR A=SELECTEDVALUE('data'[Year]) VAR C=MAX(data[Index]) VAR B=CALCULATE( SUM('data'[Sales Value]),FILTER(ALL('data'), 'data'[Year]=A&&'data'[Index]<=C) ) RETURN IF( MAX('data'[Index])=1, 0, B)

If you have any other questions, check out the pbix file I uploaded, I hope it helps!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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