Forum Discussion
Running sum in a week
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 |
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:
1 Reply
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: