cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rajpatelzebra
Regular Visitor

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.

2021-11-15Capture.JPG

 

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.

1 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
        )

 

smpa01_0-1637096517407.png

 

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


View solution in original post

10 REPLIES 10
smpa01
Super User
Super User

@rajpatelzebra  you would need a row identifier like following 

smpa01_0-1636999505392.png

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] ) )

 

smpa01_2-1636999711208.png

 

 

pbix is attached

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


@rajpatelzebra  did you try the above ?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


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. 

 

OrderXNumberSales Part NoIndexTarget Date/TimeSales QtyAvailable Qty Measure 
N2119910153011/15/2021 0:0080033470     120,445,448.00
N21056101678304/5/2022 0:001700000     120,445,448.00
N21057101678318/30/2022 0:001700000        81,088,724.00
N21055101679609/9/9999 0:001700000     120,445,448.00
N2108410204012/6/2021 0:00150000875000     120,445,448.00
N210611032704/5/2022 0:0080000234000     120,445,448.00
N210621033802/1/2022 0:0060003500     120,445,448.00
N210581033815/31/2022 0:0060003500        81,088,724.00
N210641033828/2/2022 0:0060003500        67,459,272.00
N2105910338311/1/2022 0:0060003500        56,534,822.00
N210601033904/5/2022 0:00500000     120,445,448.00
N2120510344011/17/2021 0:00101000     120,445,448.00
N2106710352012/1/2021 0:00672000     120,445,448.00
N211711035213/1/2022 0:00672000        81,088,724.00
N2080910388011/16/2021 0:0048000057030000     120,445,448.00
N2108810388111/19/2021 0:00200000057030000        81,088,724.00
N2114910388211/22/2021 0:00700000057030000        67,459,272.00
N2089010388311/23/2021 0:00400000057030000        56,534,822.00
N2087710388412/3/2021 0:00100000057030000        48,928,622.00
N2110410388512/6/2021 0:00224000057030000        44,328,422.00
N2114810388612/7/2021 0:00500000057030000        33,688,222.00
N211851038871/14/2022 0:00400000057030000        25,488,022.00
N20721038899/9/9999 0:00340000057030000        12,265,822.00
N2080110388109/9/9999 0:00300000057030000          8,865,822.00
N211581038889/9/9999 0:00220000057030000        18,476,622.00
N2108910389011/19/2021 0:00100000030920000     120,445,448.00
N2087710389112/3/2021 0:00100000030920000        81,088,724.00
N207210389189/9/9999 0:001220000092760000        25,488,022.00
N211111038989/9/9999 0:00400000030920000        18,476,622.00
N212071038999/9/9999 0:00720000092760000        48,928,622.00
N2121010416011/16/2021 0:004002360     120,445,448.00
N2114710459012/7/2021 0:003000000340000     120,445,448.00
N2100810463012/13/2021 0:0050100007770000     120,445,448.00
N2107610491011/30/2021 0:004354000154000     120,445,448.00
N2014210560012/28/2021 0:002500     120,445,448.00
N182591056309/9/9999 0:00200003190     120,445,448.00
N1995610570011/15/2021 0:0040000     120,445,448.00
N1995610570112/13/2021 0:0040000        81,088,724.00
N210621058304/5/2022 0:00200000     120,445,448.00
N2118910624011/15/2021 0:0010000101270     120,445,448.00
N2116210634011/15/2021 0:0080000     120,445,448.00
N2116210634111/22/2021 0:0080000        81,088,724.00
N2116210634212/1/2021 0:0080000        67,459,272.00
N2098210661011/29/2021 0:0018700001870000     120,445,448.00
N1995710664011/15/2021 0:002400024000     120,445,448.00
N1995710664112/13/2021 0:002400024000        81,088,724.00
N2076210666011/15/2021 0:0020017250     120,445,448.00
N2076210666111/22/2021 0:0020017250        81,088,724.00
N2076210666212/21/2021 0:0020017250        67,459,272.00
N207621066631/19/2022 0:0020017250        56,534,822.00
N207621066642/21/2022 0:0020017250        48,928,622.00
N207621066653/23/2022 0:0020017250        44,328,422.00
N207621066664/20/2022 0:0020017250        33,688,222.00
N1922110666159/9/9999 0:002220034500        18,476,622.00
N2117810683011/17/2021 0:0012000005390000     120,445,448.00
N2107410683111/29/2021 0:0012000005390000        81,088,724.00
N2117710683211/29/2021 0:0012000005390000        67,459,272.00
N2107510683312/13/2021 0:0012000005390000        56,534,822.00
N211191068341/10/2022 0:0012000005390000        48,928,622.00
N211571068351/31/2022 0:0012000005390000        44,328,422.00
N211921068362/7/2022 0:0012000005390000        33,688,222.00
N2069610791011/17/2021 0:003000000520000     120,445,448.00
N20721082409/9/9999 0:001501081920     120,445,448.00
N2114910832011/22/2021 0:0020000021070000     120,445,448.00
N211851083211/14/2022 0:00400000021070000        81,088,724.00
N20721083229/9/9999 0:0025000021070000        67,459,272.00
N206951083701/26/2022 0:005000000     120,445,448.00
N211121083714/20/2022 0:005100000        81,088,724.00
N2107010843012/6/2021 0:0020000000     120,445,448.00
N212081084509/9/9999 0:0010000001050000     120,445,448.00
N2080910863011/16/2021 0:0030000090000     120,445,448.00
N2080910864011/16/2021 0:001720000160000     120,445,448.00
N2080910866011/16/2021 0:0045000050000     120,445,448.00
N2080910867011/16/2021 0:00610000110000     120,445,448.00
N20721087709/9/9999 0:00120000     120,445,448.00
N2082910903011/16/2021 0:0055000004740000     120,445,448.00
N212061090319/9/9999 0:0045000004740000        81,088,724.00
N2114910904011/22/2021 0:00200000560000     120,445,448.00
N210011092309/9/9999 0:0039000000     120,445,448.00
N2120510929011/17/2021 0:00100     120,445,448.00
N209251093009/9/9999 0:0046004600     120,445,448.00
N211201186011/23/2021 0:0020000001590000     120,445,448.00
N207071475011/22/2021 0:001000001250000     120,445,448.00
N20942147511/19/2022 0:001000001250000        81,088,724.00
N210651476011/23/2021 0:001000001295000     120,445,448.00
N197157201-200012/6/2021 0:00104698     120,445,448.00
N197157201-20013/28/2022 0:0052698        81,088,724.00
N211467201-20029/9/9999 0:00250698        67,459,272.00
N21209FM0-M09/9/9999 0:002500021000     120,445,448.00
N21097TBD09/9/9999 0:00400000     120,445,448.00
N21098TBD29/9/9999 0:00600000        67,459,272.00
N21099TBD19/9/9999 0:00400000        81,088,724.00

@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
        )

 

smpa01_0-1637096517407.png

 

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


Piku
Frequent Visitor

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!

smpa01
Super User
Super User

@rajpatelzebra  provide data in table format and not picture





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


@smpa01 

Sales QtyDelivered QtyReserved QtyAvailable QtyAvailable Qty minus Sales QtySales Part NoOrder NoCreated DateWanted Delivery Date/Time
1200000005390000419000010683N2107410/20/2021 0:0012/1/2021 0:00
1200000005390000419000010683N2107510/20/2021 0:0012/15/2021 0:00
1200000005390000419000010683N2111910/27/2021 0:001/12/2022 0:00
1200000005390000419000010683N2115711/3/2021 0:002/2/2022 0:00
1200000005390000419000010683N2117711/9/2021 0:0011/26/2021 0:00
1200000012000005390000419000010683N2117811/9/2021 0:0011/19/2021 0:00
1200000005390000419000010683N2119211/10/2021 0:002/9/2022 0:00
smpa01
Super User
Super User

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

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


Table with dates @smpa01 

Customer / Sales Order List 2021-11-15Capture2.JPG

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors