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 ...
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.