Forum Discussion

MicaelaTantum's avatar
MicaelaTantum
Icon for Advocate I rankAdvocate I
6 years ago
Solved

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 🤓

Need Help
Tips and Tricks
  • v-lionel-msft's avatar
    v-lionel-msft
    6 years ago

    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.

2 Replies

  • v-lionel-msft's avatar
    v-lionel-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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.