Forum Discussion

_Ma_Do_'s avatar
_Ma_Do_
Frequent Visitor
3 years ago
Solved

Calculate Remaing Value + Stock Coverage

Hi Everybody,    trying to figure sth our but so far no success with all the other calculations.    Following data is given:    Country Product Date Inventory Demand Germany 1234 01...
Need Help
Tips and Tricks
  • johnt75's avatar
    johnt75
    3 years ago

    You can create a couple of calculated columns like

    Start Inv = 
VAR CurrentDate = 'Table'[Date]
VAR Inventory = CALCULATE( SUM('Table'[Inventory]), ALLEXCEPT('Table', 'Table'[Country], 'Table'[Product]), 'Table'[Date] <= CurrentDate )
VAR Demand = CALCULATE( SUM('Table'[Demand]), ALLEXCEPT('Table', 'Table'[Country], 'Table'[Product]), 'Table'[Date] < CurrentDate )
RETURN Inventory - Demand

Coverage = IF( 'Table'[Demand] < 'Table'[Start Inv], 1, DIVIDE( 'Table'[Start Inv], 'Table'[Demand] ) )
johnt75's avatar
johnt75
Super User
3 years ago

You can create a couple of calculated columns like

Start Inv = 
VAR CurrentDate = 'Table'[Date]
VAR Inventory = CALCULATE( SUM('Table'[Inventory]), ALLEXCEPT('Table', 'Table'[Country], 'Table'[Product]), 'Table'[Date] <= CurrentDate )
VAR Demand = CALCULATE( SUM('Table'[Demand]), ALLEXCEPT('Table', 'Table'[Country], 'Table'[Product]), 'Table'[Date] < CurrentDate )
RETURN Inventory - Demand

Coverage = IF( 'Table'[Demand] < 'Table'[Start Inv], 1, DIVIDE( 'Table'[Start Inv], 'Table'[Demand] ) )
  • _Ma_Do_'s avatar
    _Ma_Do_
    Frequent Visitor
    3 years ago

    Thanks - that was the thing I was looking to do - I'll jump on the train from this one 🙂