Running Total for Groups of Products by Store

[steveborty]

Hi everyone!

After a couple of days of trial and error I come to you for some help.

I am working on a running total of the sales (SLS) based up to a certain week (expressed as year&week number) and on products for different stores. The aim is getting a cumulative total that works at product/store level either directly on power query or on a pivot thanks to a DAX formula.

The starting point is an appended query with all the different years similar to the following (Running Total column just as an example):

Store	Product	SLS	STOCK	YEARWEEK	RT
AAA	A123	0	8	202101	0
AAA	A123	3	5	202103	3
AAA	A123	5	0	202104	8
AAA	C123	2	10	202101	2
AAA	C123	3	7	202102	5
BBB	A123	1	5	202102	1
BBB	B123	1	19	202104	1
BBB	B123	4	15	202105	5
BBB	B123	6	9	202203	11
CCC	A123	0	10	202101	0
CCC	A123	1	9	202102	1
CCC	A123	4	5	202103	5
CCC	B123	6	12	202104	6
CCC	B123	2	10	202105	8
CCC	B123	0	10	202106	8
CCC	A123	1	4	202105	6
CCC	A123	-1	5	202106	5

I have tried DAX options such as the following which works for the SKU but not when I select a specific Store in the Pivot as it still gives me the total cumulative of the SKU (I suppose because of the ALLEXCEPT filter.

= CALCULATE (
    SUM ( 'Table'[Product] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Product] ),
        'Table'[YEARWEEK] <= MAX ( 'Table'[YEARWEEK] )
    )
)

 Do you have any idea on how I may fix this?

Thank you!

[v-tangjie-msft]

Hi  @steveborty ,

Try to update the measure.

Measure= CALCULATE (
    SUM ( 'Table'[Column] ),//this column depending on your needs
    FILTER (
        ALLSELECTED('Table'),
        'Table'[Product]=MAX('Table'[Product]) && 'Table'[YEARWEEK] <= MAX ( 'Table'[YEARWEEK] )
    )
)

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[sjoerdvn]

try something like this

RT = VAR lw=MAX('Table'[YEARWEEK]) RETURN  CALCULATE(SUM('Table'[SLS]), 'Table'[YEARWEEK]<lw)

[Greg_Deckler]

@steveborty See if this helps:

[steveborty]

Thank you Greg! 

Not really sure about how to implement that directly as a Pivot DAX funcion or in power query. Any suggestion?