Running sum in a week

[Sachin27031987]

Hi Guys,  Please help me in getting running sum within a week (ignoring wholesaler). I need output column to get it from below table 

Wholesaler	Week	Qty	Output
A	1	10	35
B	1	20	25
C	1	5	5
A	2	20	45
B	2	20	25
C	2	5	5

[Anonymous · Accepted solution]

Hi @Sachin27031987 

I have understood your needs, you need an Output column to calculate the cumulative data.

I created a table using your data:

Transform data > Add column > Index Column

At this point, there will be a column of serial numbers in multiple places.

Then create a calculated column:

-------------------------------------------------------------------

Output =

      SUMX(

            FILTER(

                'Table',

                'Table'[Index] >= EARLIER('Table'[Index]) && 'Table'[Week] = EARLIER('Table'[Week])

            ),

            'Table'[Qty]

        )

-------------------------------------------------------------------

This code uses the Filter function to filter from the bottom up, the Week column to set the range, and finally the SUMX function to iteratively aggregate.

The result is as follow: