cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.