topic Re: How to calculate the ending inventory with cumulative inventory movement value in DAX Commands and Tips

How to calculate the ending inventory with cumulative inventory movement value

naoyixue1 — Tue, 22 Sep 2020 03:28:39 GMT

Hey there,

I have a problem to calculate the ending inventory at below format (Row = item, column = month, value = demands, WorkOrder, On Hand Inv)

Inventory movement = Work Order qty + On hand inventory - demands

The ending inventory = CALCULATE(CALCULATE([Movement],FILTER(ALLSELECTED('Fact'),'Fact'[Date]<=MAX('Fact'[Date]))))

But it works when i only put the date in the row, but not correct when i put item in row, and month in the column. (see below). I think i might also add some dax such as allselected to item level. Would you kindly help? Thanks a lot!

Re: How to calculate the ending inventory with cumulative inventory movement value

amitchandak — Tue, 22 Sep 2020 04:29:03 GMT

naoyixue1 , in such case, you should have date in a separate table. Like date table. so all/ allselected on the date table will not impact relation with item

The ending inventory = CALCULATE(CALCULATE([Movement],FILTER(ALLSELECTED('Date'),'Date'[Date]<=MAX('Date'[Date]))))

I also think it should be like

Inventory movement = Work Order qty - demands

The ending inventory = [intial On hand inventory]+ CALCULATE(CALCULATE([Movement],FILTER(ALLSELECTED('Date'),'Date'[Date]<=MAX('Date'[Date]))))

initial On hand inventory; inventory on the start date

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

Appreciate your Kudos.

Re: How to calculate the ending inventory with cumulative inventory movement value

naoyixue1 — Tue, 22 Sep 2020 20:39:15 GMT

Thanks Sir for your inputs! Just send private message to you to see whether you have time to look at my table setting. Thanks!