Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
Anonymous
Not applicable

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

‎11-15-2021 08:36 AM

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.

Labels:
Message 1 of 11
2,191 Views
0
1 ACCEPTED SOLUTION
smpa01
Super User
Super User
In response to Anonymous

‎11-16-2021 01:03 PM

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

 

smpa01_0-1637096517407.png

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

_rn.pbix
Message 9 of 11
1,982 Views
10 REPLIES 10
smpa01
Super User
Super User

‎11-15-2021 10:10 AM

@Anonymous  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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
_runningSubtraction.pbix
Message 6 of 11
2,086 Views
0
smpa01
Super User
Super User
In response to smpa01

‎11-16-2021 11:42 AM

@Anonymous  did you try the above ?

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Message 7 of 11
2,025 Views
0
Anonymous
Not applicable
In response to smpa01

‎11-16-2021 12:43 PM

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
Message 8 of 11
2,005 Views
0
smpa01
Super User
Super User
In response to Anonymous

‎11-16-2021 01:03 PM

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

 

smpa01_0-1637096517407.png

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
_rn.pbix
Message 9 of 11
1,983 Views
Piku
Frequent Visitor
In response to smpa01

‎10-11-2022 09:41 PM

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

Message 11 of 11
1,251 Views
0
Anonymous
Not applicable
In response to smpa01

‎11-17-2021 05:20 AM

This is extremely helpful thank you!

Message 10 of 11
1,933 Views
0
smpa01
Super User
Super User

‎11-15-2021 09:34 AM

@Anonymous  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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Message 4 of 11
2,128 Views
0
Anonymous
Not applicable
In response to smpa01

‎11-15-2021 09:37 AM

@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
Message 5 of 11
2,117 Views
0
smpa01
Super User
Super User

‎11-15-2021 09:01 AM

@Anonymous  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!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Message 2 of 11
2,175 Views
0
Anonymous
Not applicable
In response to smpa01

‎11-15-2021 09:27 AM

Table with dates @smpa01 

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

Message 3 of 11
2,139 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All
Top Solution Authors
View All