Forum Discussion

Hammarberg's avatar
Hammarberg
Frequent Visitor
9 years ago

Inventory Calculation (removing negative results)

Hello everyone,

 

I have a puzzle that I'm not able to solve. I've scanned the site for help and got as far as creating the cumulative measures needed, but I don't get the correct picture in the end due to negative values. Every time Demand forecast is higher than the inventory+replenishment I end up with negative values, which for an inventory forecast is very unlikely...

 

Through the use of a simple IF statement I have been able to get the correct values when looking at individual products, but it doesn't add up correctly when I aggregate the data.

 

Below are the two different calculations, one with the IF statment (Running inventory v2), and one without.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

This is the calculation for cumulative Inventory:

Cumulative Inventory = CALCULATE(SUM(Inventory[INVENTORY]);FILTER(ALL(Dates[Date]);Dates[Date]<=MAX(Dates[Date])))

 

This is the same calculation for Production/purchase:

Cumulative Production = CALCULATE(SUM(Production[PRODUCTION]);FILTER(ALL(Dates[Date]);Dates[Date]<=MAX(Dates[Date])))

 

And then for the demand forecast:

Cumulative Forecast = CALCULATE(SUM(Forecast[FORECAST LV]);FILTER(ALL(Dates[Date]);Dates[Date]<=MAX(Dates[Date])))

 

Finaly I put them together, this is the one is able to generate stock balance below zero:

Running inventory = CALCULATE([Cumulative Inventory]+[Cumulative Production]-[Cumulative Forecast];FILTER(ALL(Dates[Date]);Dates[Date]<=MAX(Dates[Date])))

 

And this one provides zero value as long as I filter on specific products, the final row - aggregated view - still calculates with negative values:

Running inventory v2 = IF(CALCULATE([Cumulative Inventory]+[Cumulative Production]-[Cumulative Forecast];FILTER(ALL(Dates[Date]);Dates[Date]<=MAX(Dates[Date])))<0;0;CALCULATE([Cumulative Inventory]+[Cumulative Production]-[Cumulative Forecast];FILTER(ALL(Dates[Date]);Dates[Date]<=MAX(Dates[Date]))))

 

 

Anyone know how to take into considuration that the lowest possible value always is zero for each individual product?

 

 

 

Thanks in advance!

Need Help

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    9 years ago

    first not sure if you still need to do the cummulative total on running inventory

    Running inventory = [Cumulative Inventory]+[Cumulative Production]-[Cumulative Forecast]
    • Hammarberg's avatar
      Hammarberg
      Frequent Visitor
      9 years ago

      Thanks for commenting. Could you elaborate? I've tried different methods, and as far as I know now, the cumulative totals are needed for all values used to calculate dependent values on a time line.

       

      Thanks.

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi Hammarberg,

         

        I'd like to suggest you use "ALLSELECT" to replace "ALL" function.

         

        Measures:

         

        Cumulative Inventory = SUM(Inventory[INVENTORY])
 
Cumulative Production = SUM(Production[PRODUCTION])

Cumulative Forecast =SUM(Forecast[FORECAST LV])

Running inventory = CALCULATE([Cumulative Inventory]+[Cumulative Production]-[Cumulative Forecast];FILTER(ALLSELECT(Dates);Dates[Date]<=MAX(Dates[Date])))

         

         

        If above not help, can you please share some sample data to test?

         

        Regards,

        Xiaoxin Sheng