Forum Discussion

sinu's avatar
sinu
Regular Visitor
7 years ago

Cumulative inventory

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      sinu
      Regular Visitor
      7 years ago

      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.