Forum Discussion

Capstone's avatar
Capstone
Resolver I
9 years ago
Solved

Inventory Calculation

 

 

I need to make a fairly simple calculation involving time intelligence measures but Im struggling with it. Bascially in Excel the calculation is as follows:

 

Monthly Inventory(C19) = Opening stock (C17) + Current period accumulated (C18) - Current period depleted (C4)

       = 375000 +839699 -  713425

       = 501,274

 

The data starts from January 2017 and hence C17 is fixed for that month.

 

For the next period onwards the calculation is (for example D19)

  D19 = D18 + Inventory at end of last month(C19) - D4 

         = 702182 +501274 - 644384 

         = 559072

 

I have a date table with all dates ranging from 01/01/2017 to 31/12/2025 and the Years, months, quarters to go with it. And a Facts table which is connected to the Date table with the Date column. I just want the monthly change in inventory, so was wondering what is the best way to go about it ?

 

 

Abhi

 

Need Help
  • Capstone's avatar
    Capstone
    9 years ago

    I managed to solve it. This is how I did it. I defined a measure which gave me the first month of the data

     

    00_MinMonthIndex = Min('Date'[MonthIndex])

     

    Using this I calculcalated a measure for opening inventory

     

    01_Opening Pit Inventory At Start = If([00_MinMonthIndex] =1,375000,0)

     

    I then calculated monthly inventory change as

     

    02_ Inventory change = [00_Opening Pit Inventory At Start] + FactTable(Current period accumulated) - FactTable(Current period depleted)

     

    Finally got the cumulative inventory

     

    03_Total Inventory = CALCULATE (
    [02_Inventory change],
    FILTER (
    ALL ( 'Date'[Date]),
    'Date'[Date] <= MAX ( ( 'Date'[Date] ) )))

     

    It took me a while to arrive at the solution because I was focussed on time intelligence formulas but in the end it was just a basic cumulative calculation which did the trick.

     

    Abhi

3 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi Capstone,

     

    Could you post the structure of your facts table with some sample data? So that we can help with the measure.:smileyhappy:

     

    Regards

    • Capstone's avatar
      Capstone
      Resolver I
      9 years ago

      I managed to solve it. This is how I did it. I defined a measure which gave me the first month of the data

       

      00_MinMonthIndex = Min('Date'[MonthIndex])

       

      Using this I calculcalated a measure for opening inventory

       

      01_Opening Pit Inventory At Start = If([00_MinMonthIndex] =1,375000,0)

       

      I then calculated monthly inventory change as

       

      02_ Inventory change = [00_Opening Pit Inventory At Start] + FactTable(Current period accumulated) - FactTable(Current period depleted)

       

      Finally got the cumulative inventory

       

      03_Total Inventory = CALCULATE (
      [02_Inventory change],
      FILTER (
      ALL ( 'Date'[Date]),
      'Date'[Date] <= MAX ( ( 'Date'[Date] ) )))

       

      It took me a while to arrive at the solution because I was focussed on time intelligence formulas but in the end it was just a basic cumulative calculation which did the trick.

       

      Abhi

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        9 years ago

        Hi Capstone,

         

        Great to hear the problem got resolved! Could you accept your reply as solution to help others who may have similar issue easily find the answer and close this thread?:smileyhappy:

         

        Regards