cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Capstone Member
Member

Inventory Calculation

 Capture.PNG

 

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Capstone Member
Member

Re: Inventory Calculation

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 3
v-ljerr-msft Super Contributor
Super Contributor

Re: Inventory Calculation

Hi @Capstone,

 

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

 

Regards

Capstone Member
Member

Re: Inventory Calculation

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 Super Contributor
Super Contributor

Re: Inventory Calculation

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?Smiley Happy

 

Regards