cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Accummulated total, but start from zero

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

 

ItemDateSOH StartManufacturing Qty Units Sold SOH Pre PurchasePurchase QuantitySOH  After Purchase
k90008A01 January 2019               -  00000
k90008A16 January 2019 09-990
k90008A17 January 2019 90000
k90008A18 January 2019 26026026
k90008A15 May 2019 10036036
k90008A27 May 2019 0234034
k90008A09 July 2019 9043043
k90008A11 July 2019 0934034
k90008A08 February 2021 9043043
k90008A10 February 2021 5048048
k90008A12 February 2021 01038038
k90008A10 March 2021 9047047
5 REPLIES 5
Highlighted
Microsoft
Microsoft

Re: Accummulated total, but start from zero

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]

 

 Pre and After.JPG

Best,
Paul

Highlighted
Helper III
Helper III

Re: Accummulated total, but start from zero

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.

 

 

Highlighted
Microsoft
Microsoft

Re: Accummulated total, but start from zero

@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

Highlighted
Helper III
Helper III

Re: Accummulated total, but start from zero

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

Highlighted
Helper III
Helper III

Re: Accummulated total, but start from zero

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

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Kudoed Authors