cancel
Showing results for
Did you mean: Frequent Visitor

## Running Calculation (Date part will run out)

Hello 😁

I would like some help with cracking a running total formula that I have been struggling with for a while.

I basically need to show when each part number will run out...

These are my columns;

• OH ( on hand) quanity which we have on site.
• Req QTY ( Required total amount ) which tells me how many of this part we need
• Item which are the part numbers
• Sales Order which is a unique product number where the part will be build on
• Actual Lauch Date which tells me when the product will be used in the factory

I have added these columns into a table to visualise. I would like another column on the end which will take the total amount of this part number (131,306.00) and take each row away from the total. Once it reaches 0, i will then create some sort of visual to highlight the actual launch date.

I have tried a few rolling total formulas but it does not want to take the total amount and subtract it, instead it seems to formulate a row at a time.

Any suggestions/ help will be appreciated

Kind regards,

Micaela 🤓

1 ACCEPTED SOLUTION  Community Support

"I would like another column on the end which will take the total amount of this part number (131,306.00) and take each row away from the total. Once it reaches 0, i will then create some sort of visual to highlight the actual launch date. "

Create measures like this:

``````Measure 3 =
CALCULATE(
SUM(Sheet4[REQD_QTY]),
FILTER(
ALLSELECTED(Sheet4),
Sheet4[Sales Order] <= MAX(Sheet4[Sales Order])
)
)``````
``````Measure 4 =
CALCULATE(
SUM(Sheet4[REQD_QTY]),
ALL(Sheet4)
)``````
``Measure 5 = [Measure 4] - [Measure 3]``

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 REPLIES 2  Community Support

"I would like another column on the end which will take the total amount of this part number (131,306.00) and take each row away from the total. Once it reaches 0, i will then create some sort of visual to highlight the actual launch date. "

Create measures like this:

``````Measure 3 =
CALCULATE(
SUM(Sheet4[REQD_QTY]),
FILTER(
ALLSELECTED(Sheet4),
Sheet4[Sales Order] <= MAX(Sheet4[Sales Order])
)
)``````
``````Measure 4 =
CALCULATE(
SUM(Sheet4[REQD_QTY]),
ALL(Sheet4)
)``````
``Measure 5 = [Measure 4] - [Measure 3]``

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  Super User

Take a peak at Days of Supply: https://community.powerbi.com/t5/Quick-Measures-Gallery/Days-of-Supply/m-p/635656#M318

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition  DAX is easy, CALCULATE makes DAX hard... Announcements #### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day! #### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference. Top Solution Authors
Top Kudoed Authors
Users online (1,662)