Forum Discussion
Power BI Dax? - Subtract from Running Total Power Query, if statement, new measure or quick measure
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.
Anonymous 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 )
10 Replies
smpa01Community Champion
Anonymous please provide sample date and please be clear about whether you expect this through
Power Query calculated column/ DAX calculated column/DAX measure?
Table with dates smpa01
Customer / Sales Order List
- smpa01
Anonymous provide data in table format and not picture
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
Anonymous 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
Anonymous did you try the above ?
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