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
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!

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Nov 13, 2023

    steveborty 

    If not to ignore filters why RT doesn't work?

    RT filtered:=VAR maxW = MAX(DB[YEARWEEK])
	RETURN CALCULATE( SUM(DB[SLS]), DB[YEARWEEK]<=maxW )

    Or you'd like to ignore some filters?