Cumulative inventory

[sinu]

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

[Anonymous]

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

[sinu]

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.