topic Cumulative inventory in DAX Commands and Tips

Cumulative inventory

sinu — Thu, 11 Jul 2019 17:21:49 GMT

I am looking for DAX to sum quantity field in Inventory table from the date begining in the database to the end date of every month. This is to know what was the stock available in previous months as option selected as how many months back from current month.

April 2019 -500
April 2019 - 200
May 2019 - 600
Jun 2019. - 100

Dash board would be

April 2019 - 700
May 2019. - 600
Jun. 2019 - 1400

Re: Cumulative inventory

Anonymous — Sat, 13 Jul 2019 13:50:16 GMT

Your model must have a proper Date table (calendar). Then, you'd do:

Goods in Stock =
var __lastDateVisible = MAX( Calendar[Date] )
return
calculate(
    [Quantity],
    Calendar[Date] <= __lastDateVisible
)

where [Quantity] should be a measure that returns the sum of quantity for any selection of attributes from your dimensions.

Best

Darek

Re: Cumulative inventory

sinu — Mon, 29 Jul 2019 13:04:15 GMT

Thank you for the support!

I tried the option you shared but did not work as expected. I am looking for an option to do inventory analysis for the last 'n' months seletced.

Item A

Item B

Item A - 100- March 2nd 2017

Item B - 200 - March 5th 2017

Item A - 300 - May 15th 2018

Item A - 400 - Dec 20th 2018

Item A - 500 - April 30th 2019

Item A - 400 - May 14th 2019

Item A - 200 - Jun 12th 2019

I am expecting an output as follows if I need to do analysis from july to back April 2019

April 2019 - 1500(100+200+300+400+500)

May 2019 - 1500+400=1900

Jun 2019 -1900+200 = 2100

July 2019 - 2100+0 = 2100

It need's to consider the entires from the very begining date to the end of months displays.

Thank you in advance.