Forum Discussion

steveborty's avatar
steveborty
Copper Contributor
Sep 10, 2023
Solved

Running Total for different products and stores

Hi everyone!   After a couple of days of trial and error I come to you for some help. I am trying to compute the running total of the sales (SLS) up to a certain week (expressed as year&week numbe...
BI & Data Analysis
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Sep 10, 2023

    steveborty 

    Not sure what is desired output. With

    slsRT:=CALCULATE (
    SUM ( 'Sales'[SLS] ),
    FILTER (
        ALLEXCEPT ( 'Sales', 'Sales'[Product], Sales[Store] ),
        'Sales'[YEARWEEK] <= MAX ( 'Sales'[YEARWEEK] )
    )
)

    it's like

steveborty's avatar
steveborty
Copper Contributor
Sep 10, 2023

SergeiBaklan That's great thank you!
There is one (hopefully) last complication: my real database includes many more columns that are product attributes (i.e. material, color) which I may use as filters when working with the Pivot, is there a way to take this into account in your formula without having to add all the columns into the ALLEXCEPT section?

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 11, 2023

steveborty 

Sorry for some delay. You may use

filterRT :=
VAR maxW =
    MAX ( Sales[YEARWEEK] )
RETURN
    CALCULATE (
        SUM ( 'Sales'[SLS] ),
        VALUES ( 'Sales'[Product] ),
        'Sales'[YEARWEEK] <= maxW
    )

For the sample

I added STOCK filtering into previous measure

slsRT :=
CALCULATE (
    SUM ( 'Sales'[SLS] ),
    FILTER (
        ALLEXCEPT ( 'Sales', 'Sales'[Product], Sales[Store], Sales[STOCK] ),
        'Sales'[YEARWEEK] <= MAX ( 'Sales'[YEARWEEK] )
    )
)

both give the same result.

 

  • steveborty's avatar
    steveborty
    Copper Contributor
    Oct 23, 2023

    Hi SergeiBaklan!

     

    Sorry for my very late reply. I've been using your slsRT method and it works perfectly. Yet, my database contains many additional attributes that I may eventually need as filters. Is there a way to change the red code

     

    slsRT :=
    CALCULATE (
    SUM ( 'Sales'[SLS] ),
    FILTER (
    ALLEXCEPT ( 'Sales', 'Sales'[Product], Sales[Store], Sales[STOCK] ),
    'Sales'[YEARWEEK] <= MAX ( 'Sales'[YEARWEEK] )
    )
    )

     

    in order to state something like "all the columns except the YEARWEEK". I tried to use two nested ALLEXCEPT, but it does not work.

     

    Many thanks!

      • steveborty's avatar
        steveborty
        Copper Contributor
        Nov 10, 2023

        SergeiBaklan I did, but it seems to be resetting when the new year begins if I use a broader set of data, while I'd like the running total to take into account the whole period.

        You can find attached a test, as you can see I may need to see the running totals grouped by store or directly using the product model instead of the product code, that's why in the slsRT formula I would have to insert all the necessary columns.

         

        Thanks a lot!

Resources