Visualizing Cumulative Sum with Year-Month Filters

[DavideProtasi]

Hello Everyone,

I'm struggling trying to visualize warehouse stock levels with Year-Month filters.

The one below is the data table of the stock in/out movements for two items (assuming no stock in the warehouse before 17/11/2022)

ITEM	Date	Stock In/Out
1	17/11/2022	2
1	28/11/2022	1
2	14/11/2022	7
1	16/12/2022	-1
2	14/12/2022	-1
1	03/01/2023	2
1	15/01/2023	3
2	27/01/2023	-2
1	15/02/2023	9
2	17/02/2023	-2

No problems visualizing stock levels in a bar chart with 

RangeSum(Above(Sum(Stock In/Out), 0,RowNo())) 

Year-Month	Stock In/Out No Filters	Stock Level No Filters
2022-nov	10	10
2022-dic	-2	8
2023-gen	3	11
2023-feb	7	18

but only if I don't apply filters on the bars dimension (year-month)

How can I visualize stock levels of just some months without losing the information about stock movements of the other months?

I'd like to filter, for example, from dec 2022 to feb 2023, and obtain stock levels for item 1 (data below)

Year-Month	Stock In/Out Item 1 Dic2022-Feb2023	Stock Level Item 1 Dic2022-Feb2023
2022-dic	-1	2
2023-gen	5	7
2023-feb	9	16

Right now this is what I get (november's data not used in the cumulative sum)

Year-Month	Stock In/Out Item 1 Dic2022-Feb2023	Stock Level Item 1 Dic2022-Feb2023
2022-dic	-1	-1
2023-gen	5	4
2023-feb	9	13

Let me know if you need any additional information about and thank you for your help!

[jcmachado]

In order to visualize stock levels with Year-Month filters without losing the information about stock movements of the other months, you can create a variable that holds the cumulative sum of stock levels up to the current month, and use that variable to calculate the stock levels for the selected months. Here's an example of how you can do this:

1. Create a variable 'Cumulative Sum' with the following expression: =RangeSum(Above(Sum({$<[Year-Month]={"<=$(=Max([Year-Month]))"}>} Stock In/Out), 0, RowNo()))

2. Create another variable 'Stock Level' with the following expression: =Sum({$<[Year-Month]={">=$(=Min([Year-Month])), <=$(=Max([Year-Month]))"}>} Stock In/Out) + $(Cumulative Sum)

3. Create a chart, where on the X axis you put the Year-Month, and on the Y axis the Stock Level variable.

The variable 'Cumulative Sum' holds the cumulative sum of the stock levels up to the current month, including the months outside of the selected range. The variable 'Stock Level' calculates the stock levels for the selected months by adding the stock movements within the selected range to the cumulative sum up to the current month.

By using this approach, the stock level of the selected months will be calculated based on the stock level of all the previous months, even if they are not included in the selected range. This way, you will not lose the information about stock movements of the other months.