Accummulated total, but start from zero

[vrossouw]

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

[V-pazhen-msft]

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

[vrossouw]

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.

[V-pazhen-msft]

@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

[vrossouw]

Hi, I have saved the excel file in this downloadable link.

Excel file with workings 

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

[vrossouw]

Hi,  I have calculated the Purchase Qty outside of Power BI for now.