Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
@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
)
@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
@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 |
@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
)
Hi ,
All of my values are coming from different tables and cant use index. What would be the possible approach in this case
This is extremely helpful thank you!
@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 |
@Anonymous please provide sample date and please be clear about whether you expect this through
Power Query calculated column/ DAX calculated column/DAX measure?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |