Power BI Dax? - Subtract from Running Total Power Query, if statement, new measure or quick measure

[rajpatelzebra]

I am trying to see what is the easiest way to do a running subtraction. If statement, new measure or quick measure in Power Bi Dax

Sales Qty is 1.2 million (1,200,000) for each order. See table attached.

I want to subtract for every order from the available qty so the Available Qty decreases for every new order listed with the same part.

Available Qty" should 5,390,000 --> 4,190,000 --> 2,990,000 --> 1,790,000, --> 590,000, --> -610,000, --> -1,810,000 etc.

Would like to do this in Power Query, if statement, new measure or quick measure if possible?

My goal is to get a new Column Created Called "Actual Available Qty" should 5,390,000 --> 4,190,000 --> 2,990,000 --> 1,790,000, --> 590,000, --> -610,000, --> -1,810,000 etc.

[smpa01]

@rajpatelzebra  you would need a row identifier like following 

Then you can write a measure like following

Measure = 
CALCULATE ( SUM ( t1[Available Qty] ), FILTER ( ALL ( t1 ), t1[Index] = 1 ) )
    - CALCULATE ( SUM ( t1[Sales Qty] ), ALL ( t1 ), t1[Index] < MAX ( t1[Index] ) )

pbix is attached

[smpa01]

@rajpatelzebra  did you try the above ?

[rajpatelzebra]

I see what you did and I added it in following column. However, I have to another layer detail because the order list has multiple sale part numbers so I created an group by index for each sales part number. 

However, how do i adjust the formula to work with multiple part number tables appended together. 

OrderXNumber	Sales Part No	Index	Target Date/Time	Sales Qty	Available Qty	Measure
N21199	10153	0	11/15/2021 0:00	800	33470	120,445,448.00
N21056	1016783	0	4/5/2022 0:00	170000	0	120,445,448.00
N21057	1016783	1	8/30/2022 0:00	170000	0	81,088,724.00
N21055	1016796	0	9/9/9999 0:00	170000	0	120,445,448.00
N21084	10204	0	12/6/2021 0:00	150000	875000	120,445,448.00
N21061	10327	0	4/5/2022 0:00	80000	234000	120,445,448.00
N21062	10338	0	2/1/2022 0:00	6000	3500	120,445,448.00
N21058	10338	1	5/31/2022 0:00	6000	3500	81,088,724.00
N21064	10338	2	8/2/2022 0:00	6000	3500	67,459,272.00
N21059	10338	3	11/1/2022 0:00	6000	3500	56,534,822.00
N21060	10339	0	4/5/2022 0:00	50000	0	120,445,448.00
N21205	10344	0	11/17/2021 0:00	10	1000	120,445,448.00
N21067	10352	0	12/1/2021 0:00	67200	0	120,445,448.00
N21171	10352	1	3/1/2022 0:00	67200	0	81,088,724.00
N20809	10388	0	11/16/2021 0:00	480000	57030000	120,445,448.00
N21088	10388	1	11/19/2021 0:00	2000000	57030000	81,088,724.00
N21149	10388	2	11/22/2021 0:00	7000000	57030000	67,459,272.00
N20890	10388	3	11/23/2021 0:00	4000000	57030000	56,534,822.00
N20877	10388	4	12/3/2021 0:00	1000000	57030000	48,928,622.00
N21104	10388	5	12/6/2021 0:00	2240000	57030000	44,328,422.00
N21148	10388	6	12/7/2021 0:00	5000000	57030000	33,688,222.00
N21185	10388	7	1/14/2022 0:00	4000000	57030000	25,488,022.00
N2072	10388	9	9/9/9999 0:00	3400000	57030000	12,265,822.00
N20801	10388	10	9/9/9999 0:00	3000000	57030000	8,865,822.00
N21158	10388	8	9/9/9999 0:00	2200000	57030000	18,476,622.00
N21089	10389	0	11/19/2021 0:00	1000000	30920000	120,445,448.00
N20877	10389	1	12/3/2021 0:00	1000000	30920000	81,088,724.00
N2072	10389	18	9/9/9999 0:00	12200000	92760000	25,488,022.00
N21111	10389	8	9/9/9999 0:00	4000000	30920000	18,476,622.00
N21207	10389	9	9/9/9999 0:00	7200000	92760000	48,928,622.00
N21210	10416	0	11/16/2021 0:00	400	2360	120,445,448.00
N21147	10459	0	12/7/2021 0:00	3000000	340000	120,445,448.00
N21008	10463	0	12/13/2021 0:00	5010000	7770000	120,445,448.00
N21076	10491	0	11/30/2021 0:00	4354000	154000	120,445,448.00
N20142	10560	0	12/28/2021 0:00	250	0	120,445,448.00
N18259	10563	0	9/9/9999 0:00	20000	3190	120,445,448.00
N19956	10570	0	11/15/2021 0:00	4000	0	120,445,448.00
N19956	10570	1	12/13/2021 0:00	4000	0	81,088,724.00
N21062	10583	0	4/5/2022 0:00	20000	0	120,445,448.00
N21189	10624	0	11/15/2021 0:00	10000	101270	120,445,448.00
N21162	10634	0	11/15/2021 0:00	8000	0	120,445,448.00
N21162	10634	1	11/22/2021 0:00	8000	0	81,088,724.00
N21162	10634	2	12/1/2021 0:00	8000	0	67,459,272.00
N20982	10661	0	11/29/2021 0:00	1870000	1870000	120,445,448.00
N19957	10664	0	11/15/2021 0:00	24000	24000	120,445,448.00
N19957	10664	1	12/13/2021 0:00	24000	24000	81,088,724.00
N20762	10666	0	11/15/2021 0:00	200	17250	120,445,448.00
N20762	10666	1	11/22/2021 0:00	200	17250	81,088,724.00
N20762	10666	2	12/21/2021 0:00	200	17250	67,459,272.00
N20762	10666	3	1/19/2022 0:00	200	17250	56,534,822.00
N20762	10666	4	2/21/2022 0:00	200	17250	48,928,622.00
N20762	10666	5	3/23/2022 0:00	200	17250	44,328,422.00
N20762	10666	6	4/20/2022 0:00	200	17250	33,688,222.00
N19221	10666	15	9/9/9999 0:00	22200	34500	18,476,622.00
N21178	10683	0	11/17/2021 0:00	1200000	5390000	120,445,448.00
N21074	10683	1	11/29/2021 0:00	1200000	5390000	81,088,724.00
N21177	10683	2	11/29/2021 0:00	1200000	5390000	67,459,272.00
N21075	10683	3	12/13/2021 0:00	1200000	5390000	56,534,822.00
N21119	10683	4	1/10/2022 0:00	1200000	5390000	48,928,622.00
N21157	10683	5	1/31/2022 0:00	1200000	5390000	44,328,422.00
N21192	10683	6	2/7/2022 0:00	1200000	5390000	33,688,222.00
N20696	10791	0	11/17/2021 0:00	3000000	520000	120,445,448.00
N2072	10824	0	9/9/9999 0:00	150	1081920	120,445,448.00
N21149	10832	0	11/22/2021 0:00	200000	21070000	120,445,448.00
N21185	10832	1	1/14/2022 0:00	4000000	21070000	81,088,724.00
N2072	10832	2	9/9/9999 0:00	250000	21070000	67,459,272.00
N20695	10837	0	1/26/2022 0:00	500000	0	120,445,448.00
N21112	10837	1	4/20/2022 0:00	510000	0	81,088,724.00
N21070	10843	0	12/6/2021 0:00	2000000	0	120,445,448.00
N21208	10845	0	9/9/9999 0:00	1000000	1050000	120,445,448.00
N20809	10863	0	11/16/2021 0:00	300000	90000	120,445,448.00
N20809	10864	0	11/16/2021 0:00	1720000	160000	120,445,448.00
N20809	10866	0	11/16/2021 0:00	450000	50000	120,445,448.00
N20809	10867	0	11/16/2021 0:00	610000	110000	120,445,448.00
N2072	10877	0	9/9/9999 0:00	12000	0	120,445,448.00
N20829	10903	0	11/16/2021 0:00	5500000	4740000	120,445,448.00
N21206	10903	1	9/9/9999 0:00	4500000	4740000	81,088,724.00
N21149	10904	0	11/22/2021 0:00	200000	560000	120,445,448.00
N21001	10923	0	9/9/9999 0:00	3900000	0	120,445,448.00
N21205	10929	0	11/17/2021 0:00	10	0	120,445,448.00
N20925	10930	0	9/9/9999 0:00	4600	4600	120,445,448.00
N21120	1186	0	11/23/2021 0:00	2000000	1590000	120,445,448.00
N20707	1475	0	11/22/2021 0:00	100000	1250000	120,445,448.00
N20942	1475	1	1/19/2022 0:00	100000	1250000	81,088,724.00
N21065	1476	0	11/23/2021 0:00	100000	1295000	120,445,448.00
N19715	7201-200	0	12/6/2021 0:00	104	698	120,445,448.00
N19715	7201-200	1	3/28/2022 0:00	52	698	81,088,724.00
N21146	7201-200	2	9/9/9999 0:00	250	698	67,459,272.00
N21209	FM0-M	0	9/9/9999 0:00	25000	21000	120,445,448.00
N21097	TBD	0	9/9/9999 0:00	40000	0	120,445,448.00
N21098	TBD	2	9/9/9999 0:00	60000	0	67,459,272.00
N21099	TBD	1	9/9/9999 0:00	40000	0	81,088,724.00

[smpa01 · Accepted solution]

@rajpatelzebra  you would need this

Measure2 =
VAR _part =
    MAX ( t1[Sales Part No] )
RETURN
    CALCULATE (
        SUM ( t1[Available Qty] ),
        FILTER ( ALL ( t1 ), t1[Index] = 0 && t1[Sales Part No] = _part )
    )
        - CALCULATE (
            SUM ( t1[Sales Qty] ),
            ALL ( t1 ),
            t1[Index] < MAX ( t1[Index] )
                && t1[Sales Part No] = _part
        )

[Piku]

Hi ,
All of my values are coming from different tables and cant use index. What would be the possible approach in this case

[rajpatelzebra]

This is extremely helpful thank you!

[smpa01]

@rajpatelzebra  provide data in table format and not picture

[rajpatelzebra]

@smpa01 

Sales Qty	Delivered Qty	Reserved Qty	Available Qty	Available Qty minus Sales Qty	Sales Part No	Order No	Created Date	Wanted Delivery Date/Time
1200000	0	0	5390000	4190000	10683	N21074	10/20/2021 0:00	12/1/2021 0:00
1200000	0	0	5390000	4190000	10683	N21075	10/20/2021 0:00	12/15/2021 0:00
1200000	0	0	5390000	4190000	10683	N21119	10/27/2021 0:00	1/12/2022 0:00
1200000	0	0	5390000	4190000	10683	N21157	11/3/2021 0:00	2/2/2022 0:00
1200000	0	0	5390000	4190000	10683	N21177	11/9/2021 0:00	11/26/2021 0:00
1200000	0	1200000	5390000	4190000	10683	N21178	11/9/2021 0:00	11/19/2021 0:00
1200000	0	0	5390000	4190000	10683	N21192	11/10/2021 0:00	2/9/2022 0:00

[smpa01]

@rajpatelzebra  please provide sample date and please be clear about whether you expect this through

Power Query calculated column/ DAX calculated column/DAX measure?

[rajpatelzebra]

Table with dates @smpa01 

Customer / Sales Order List