Inventory age based on batch manufacturing date

Hi v-jiascu-msft,

I should give you some more background information I suppose.

My table is actually only the following records (simplified, I have way more batch numbers, probably over 100.000+). The date is the transaction date in an inventory transaction table. The ManufacturingDate is coming from a different table and has a one to one relationship to the batch number.

Based on this table and a date table containing all dates, I am able to calculate daily stock levels using the below query I mentioned earlier:

Stock Quantity:=CALCULATE(

IF(SUM(InventTrans[PhysicalQty])=0;BLANK();SUM(InventTrans[PhysicalQty]));

FILTER(ALL(DatePhysical[Date]);DatePhysical[Date] <= MAX(DatePhysical[Date])

)

)

I would then like to calculate an average batch age per day and also put the already calculated stock quantities into age groups. Because the table I posted earlier is a result from a measure and not an actual table, I'm not sure how to implement your proposed solution.

I tried calculating the batch using the same sort of query as for the stock quantity:

Batch Age:=CALCULATE(

IF([Stock Quantity]=0;BLANK();DATEDIFF(MAX([ManufacturingDate]);MAX(DatePhysical[Date]);DAY));

FILTER(ALL(DatePhysical[Date]);DatePhysical[Date] <= MAX(DatePhysical[Date])

)

)

The issue with this query lies (I think) in the MAX([ManufacturingDate]) because it's taking the highest one per date. I would need the highest one per date and per batch and sum those up.

Regards,

Bart