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: 

 

CountryProductDateInventoryDemand
Germany123401.12.20221000100
Germany123401.01.2023 200
Germany123401.02.2023 50
Germany123401.03.2023 60
Germany123401.04.2023 30
Germany123401.05.2023 40
Germany123401.06.2023 50
Germany123401.07.2023 200
Germany123401.08.2023 200
Germany123401.09.2023 80
Germany222201.12.20221000100
Germany222201.01.2023 200
Germany222201.02.2023 50
Germany222201.03.2023 60
Germany222201.04.2023 30
Germany222201.05.2023 40
Germany222201.06.2023 50
Germany222201.07.2023 0
Germany222201.08.2023 0
Germany222201.09.2023 0
Germany222201.10.2023 0
Germany222201.11.2023 0
Germany222201.12.2023 0
Poland123401.12.20221000100
Poland123401.01.2023 200
Poland123401.02.2023 50
Poland123401.03.2023 60
Poland123401.04.2023 30
Poland123401.05.2023 40
Poland123401.06.2023 50
Poland123401.07.2023 200
Poland123401.08.2023 200
Poland123401.09.2023 80
Poland222201.12.20221000100
Poland222201.01.2023 200
Poland222201.02.2023 50
Poland222201.03.2023 60
Poland222201.04.2023 30
Poland222201.05.2023 40
Poland222201.06.2023 50
Poland222201.07.2023 50
Poland222201.08.2023 100
Poland222201.09.2023 320
Poland222201.10.2023 0
Poland222201.11.2023 0
Poland222201.12.2023 0

 

And I'd like to create the Remaining stock + cover calculation to have the following result 

 

CountryProductDateInventoryDemandStart InvCoverage
Germany123401.12.20221000100

1000

1
Germany123401.01.2023 2009001
Germany123401.02.2023 507001
Germany123401.03.2023 60

650

1
Germany123401.04.2023 305901
Germany123401.05.2023 405601
Germany123401.06.2023 505201
Germany123401.07.2023 2004701
Germany123401.08.2023 2002701
Germany123401.09.2023 80700,875

 

Start Inv  = Inventory when it's the first entry. Otherwise ending inventory from prev month. 

 

Coverage will be 1 when Demand < Start Inv and when Inv < Demand then the remainer that can be covered.

 

I'd be happy to see how to create a new column in dax to have this evaluated. 

 

Thanks !

 

M

 

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

2 Replies

  • 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 🙂