Forum Discussion
Inventory calculation using DAX
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
7 Replies
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))))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
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
