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!:
Learn Power BI 2nd Edition

Announcements