Running Calculation (Date part will run out)

[MicaelaTantum]

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.

Raw table

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 🤓

[v-lionel-msft · Accepted solution]

Hi @MicaelaTantum ,

"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]

Result picture 

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.

[Greg_Deckler]

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