Inventory calculation using DAX

[Anonymous]

Hi All,

I want to calculate the Closing/Opening inventory using DAX. 

What I have:

Brand |Jan Inv
a |400
b |750
c |400

Brand |Month |Sales
a |Oct-20 |900
a |Nov-20 |500
a |Dec-20 |900
a |Jan-21 |500
a |Feb-21 |1000
b |Oct-20 |600
b |Nov-20 |700
b |Dec-20 |800
b |Jan-21 |800
b |Feb-21 |500

also a calendar table.

Required output in a Matrix

|Jan-21 | | |Feb-21 | |
Brand |Op Inv |Sales |Cl Inv |Op Inv |Sales |Cl Inv
a |400 |500 |133 |133 |800 |300
b |750 |800 |850 |850 |1000 |1,083

For the closing inventory, the calculation is like:

Op Inv + Sales - Average sales of past 3 month. 

Now in Feb, the Op. Inv will be the Jan closing inventory (In Jan, op. inv will be from the table)

It looks like a recursive kind of dax needed. 

Any help would be appreciated.

Kris

[Anonymous]

Thanks Amit for the reply.

Unfortunately this is not giving me the answer.

The Jan Inventory table is not connected with the Calendar table(I missed to mention this ealrlier)

Secondly, the closing calculation is Opening + Month sales - Average of past 3 Month Sales. I didn't find the average calculation in the DAXs.

Also you have used a hyphen in the last DAX, not sure about this one. I just replaced with a 0.

Unfortunately I won't be able to upload a sample pbix here. If you have created a pbix with the above data, could you please upload here ?

Thanks

Kris

[amitchandak]

@Anonymous , - was -1.

But I am not very clear on the structure of the inventory table. Can you confirm you have only first inventory or you have month on column

[Anonymous]

Hi Amit,

Inventory table has brand name and it's inventory value, nothing else.

HTH

Kris

[Anonymous]

Hi @Anonymous ,

I have a question about the sales value of February in the expected results you listed. According to the data in your table, the sales of Brand a in February is 1000, and the sales of Brand b is 500. Why are you here Brand a and b are 800 and 1000 respectively ? And is the OP Inv of the next month equal to the CI Inv of the previous month? There is also a recursive calculation involved, which may not be easily implemented using DAX...

Best Regards

[Anonymous]

Hi

"..According to the data in your table, the sales of Brand a in February is 1000, and the sales of Brand b is 500. Why are you here Brand a and b are 800 and 1000 respectively ? "

It's a copy paste mistake 😞

"..And is the OP Inv of the next month equal to the CI Inv of the previous month? "

Yes. I also think it requires a recursive calculation.

Kris

[Anonymous · Accepted solution]

Hi @Anonymous ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want.

Best Regards

[amitchandak]

@Anonymous , You need to create a common date table and have month year there

Assuming only have inventory for Jan

Opening = CALCUALTE(Sum(Inventory[Inv]),all(Date))

Cumm Sales = CALCULATE(SUM(Sales[Sales]),filter(allselected(date),date[date] <=max(date[date])))

closing Inv = [Opening] -[Cumm Sales]
New opening = [Opening] - CALCULATE(SUM(Sales[Sales]),filter(allselected(date),date[date] <=maxX(Date,dateadd(date[date],-,month))))