cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sinu Occasional Visitor
Occasional Visitor

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
1 REPLY 1
darlove Senior Member
Senior Member

Re: Cumulative inventory

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