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) = Ope...
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

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