Dax formula to calculate inventory using the production and expiration date

[Lenastray]

Thanks for any assistance. In the data, we have a date table, and a table with the columns production date, item, amount and expiration date. My request is for creating a measure that calculate the current amount for any item at any given date (considering that production of any item happens more than once), as an alternative to creating the snapshot by etl.

Thanks for the support 

[lbendlin]

Put your day column from the dates table into the visual that you want to use, and then create a measure that calculates the amount of products for each day where the production date is in the past and the expiration date is in the future.

Show some sample data and expected result.

[az38 · Accepted solution]

Hi @Lenastray 

if I understand you correct you can try to create a measure in your date table like

Measure = 
var _currentDate = MAX('DateTable'[Date])
RETURN
CALCULATE(SUM(Table[Amount]), Table[production date] <= _currentDate, Table[expiration date] >= _currentDate)

[Lenastray]

Works great. Thanks! Az38. Which filter is recommended in calculate if we want to have the inventory for every day independent if there is a production of some item or not. 

[fozart]

Did you get resolution to this follow up question? I have the same question.