Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowGet certified as a Fabric Data Engineer: Check your eligibility for a 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700. Get started
Hi,
I have the below table, where the SOH Pre purchase is the running total of SOH start + Manufacture Qty - Units Sold.
However, in some cases, we do not have stock and then have to purchase the quantity to be able to sell to the customer.
At which point our stock on hand is zero.
My problem is in line 3... where we manufacture 9 items. What should the correct formula be for SOH After Purchase? To take into account purchase stock as well?
Thank you in advance
Item | Date | SOH Start | Manufacturing Qty | Units Sold | SOH Pre Purchase | Purchase Quantity | SOH After Purchase |
k90008A | 01 January 2019 | - | 0 | 0 | 0 | 0 | 0 |
k90008A | 16 January 2019 | 0 | 9 | -9 | 9 | 0 | |
k90008A | 17 January 2019 | 9 | 0 | 0 | 0 | 0 | |
k90008A | 18 January 2019 | 26 | 0 | 26 | 0 | 26 | |
k90008A | 15 May 2019 | 10 | 0 | 36 | 0 | 36 | |
k90008A | 27 May 2019 | 0 | 2 | 34 | 0 | 34 | |
k90008A | 09 July 2019 | 9 | 0 | 43 | 0 | 43 | |
k90008A | 11 July 2019 | 0 | 9 | 34 | 0 | 34 | |
k90008A | 08 February 2021 | 9 | 0 | 43 | 0 | 43 | |
k90008A | 10 February 2021 | 5 | 0 | 48 | 0 | 48 | |
k90008A | 12 February 2021 | 0 | 10 | 38 | 0 | 38 | |
k90008A | 10 March 2021 | 9 | 0 | 47 | 0 | 47 |
If I understand your request correctly, you can simply create “SOH After Purchase” column by adding the “Purchase Quantity” column after the cumulative total.
SOH Pre =
CALCULATE (
SUM ( Table[Manufacturing Qty ] ),
FILTER ( Table, [Date] <= EARLIER ( Table[Date] ) )
) - Table[Units Sold ]
SOH after =
CALCULATE (
SUM ( Table[Manufacturing Qty ] ),
FILTER ( Table, [Date] <= EARLIER ( Table[Date] ) )
) - Table[Units Sold ] + Table[Purchase Quantity]
Best,
Paul
Hi, the problem is that the purchase quantity, is a calculated quantity.
For example if the shortfall in units are 9, such as in line 2, the 9 units must be purchased.
My problem is to add these additional 9 units then back into the running stock on hand quantity.
@vrossouw
You are able to add the additional Purchase Quantity back to the running total by simple adding even it is calculated column.
I don't really get what do you mean, can you provide a sample pbix and show us the expected result in the columns.
Best,
Paul
Hi, I have saved the excel file in this downloadable link.
I try to recreate the below columns - highlighted in red on the file, in Power BI.
Purchase | Prev Buy | check end soh |
I appreciate your time.
Regards
Hi, I have calculated the Purchase Qty outside of Power BI for now.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
146 | |
72 | |
63 | |
52 | |
51 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |