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

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
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!

Resources