Subtraction "with memory" in columns

[vipett]

I've generated a gross demand list per item per customer and month that would be input for a breakdown to sub-components.

However, as we have some stock of the same items, I want to deduct that first to generate a net demand that would go to the breakdown.

For example 1), if I have 100 pieces on stock in the first month and the demand is 120, then I want to use those 100 pieces I have in stock and only put 20 to the breakdown. (see example below, last row for item "A")

2) If I have 100 pieces on stock and the demand is 90, then I will not put anything to the breakdown, assume I will only consume from the inventory and those 10 pieces remaining will be put forward for the next month. (first rows for A in the example + all rows for B)

3) If I have 0 pieces on stock, 100 in demand, then 100 will be put forward to the next step. ( all rows for C in the example below)

Sample data below

Item	Customer	Request month	Amount	Stock today	Remaining stock	To Breakdown
A	X	2309	10	100	90	0
A	Y	2309	30	100	60	0
A	T	2309	10	100	50	0
A	K	2309	10	100	40	0
A	X	2310	20	100	20	0
A	X	2311	10	100	10	0
A	Z	2312	40	100	0	30
B	X	2309	25	500	475	0
B	K	2309	10	500	465	0
B	R	2309	50	500	415	0
B	X	2310	15	500	400	0
B	R	2310	40	500	360	0
B	X	2311	20	500	340	0
B	R	2311	20	500	320	0
B	X	2312	10	500	310	0
B	X	2401	20	500	290	0
B	X	2402	30	500	260	0
C	X	2309	10	0	0	10
C	Y	2309	15	0	0	15
C	Z	2309	30	0	0	30
C	X	2310	30	0	0	30
C	Y	2310	20	0	0	20
C	Z	2310	20	0	0	20
C	X	2311	10	0	0	10
C	Y	2311	25	0	0	25
C	X	2312	60	0	0	0

[Anonymous]

Hi @vipett ，

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) Click "transform data" to enter the power query editor and add index a column.

(3)We can create calculated columns.

Remaining stock = 
var _a='Table'[Stock today]-CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Item]=EARLIER('Table'[Item]) && 'Table'[Index]<=EARLIER('Table'[Index])))
return IF(_a<0,0,_a)

To Breakdown = 
var _a='Table'[Stock today]-CALCULATE(SUM('Table'[Amount]),FILTER('Table','Table'[Item]=EARLIER('Table'[Item]) && 'Table'[Index]<=EARLIER('Table'[Index])))
var _b= IF(_a<0,ABS(_a),0)
return IF('Table'[Stock today]=0,[Amount],_b)

(4) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[vipett]

@Anonymous Here is a simplified version: https://easyupload.io/o4dy14

[vipett]

@Anonymous  looked again at your pbix, copied the formulas and just changed the name.. it looks alright in you file but I can't seem to make it work.. everything looks the same, just that my index column is calculated from [item]&[month]&[customer].

But the index works and it is in the right order, but still, won't get the same result..

Any ideas?

For this item it is correct for the first bit, but then it starts accumulating again:

[vipett]

Thanks, looks good in theory, however my table is a crossjoined table with DAX, any way to do this without the index column or can it be done in some other way?

[Anonymous]

Hi @vipett ,

Your data doesn't look like it has an exact order or descending pattern, the index column can only be added in power query. If you want the data to be sorted in order or descending order and then calculate the final result, you can use the rankx function or the rank function to add a index column to the calculated table and then create other calculated columns. Please note that different index column may result in inconsistencies between the expected results and the output you provide.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[vipett]

Feels like I am almost there, however, I get the accumulated numbers in "To Breakdown", I would like to have just how much that is remaining.

Any ideas?

[Anonymous]

Hi @vipett ,

You can create another calculated column.

Column='Table'[Stock today] - 'Table'[accumulated numbers]

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[vipett]

@Anonymous Well, I want the first line in the picture to say 342 just like it does today, and then I want the second line to say 6571 (as all available stock was consumed for the first line).