Hi everyone,
I have a table with inventory transactions that I use to calculate movements as well as inventory levels per day.
Quantity:=SUM(InventTrans[Qty])
Stock Quantity:=CALCULATE(
IF(SUM(InventTrans[PhysicalQty])=0;BLANK();SUM(InventTrans[PhysicalQty]));
FILTER(ALL(DatePhysical[Date]);DatePhysical[Date] <= MAX(DatePhysical[Date])
)
)
My table also contains batch numbers with a manufacturing date. For all batch related inventory, I want to calculate the average age over all batches dynamically. I have tried countless functions and the closest I could get is the following. I did not even get far enough to move to averages (either using an average function or sum and divide by the distinctcount)
Batch Age:=CALCULATE(
IF([Stock Quantity]=0;BLANK();DATEDIFF(MAX([ManufacturingDate]);MAX(DatePhysical[Date]);DAY));
FILTER(ALL(DatePhysical[Date]);DatePhysical[Date] <= MAX(DatePhysical[Date])
)
)
This is giving me the following result:
| Row Labels | Batch1 | Batch2 | Batch3 | Batch4 | Grand Total |
| 01.06.2017 | 16 | | | | 16 |
| 02.06.2017 | 17 | | | | 17 |
| 03.06.2017 | 18 | | | | 18 |
| 04.06.2017 | 19 | | | | 19 |
| 05.06.2017 | 20 | | | | 20 |
| 06.06.2017 | 21 | | | | 21 |
| 07.06.2017 | 22 | | | | 22 |
| 08.06.2017 | 23 | | | | 23 |
| 09.06.2017 | 24 | 0 | | | 0 |
| 10.06.2017 | 25 | 1 | | | 1 |
| 11.06.2017 | 26 | 2 | | | 2 |
| 12.06.2017 | | 3 | | | 3 |
| 13.06.2017 | | | 0 | | 0 |
| 14.06.2017 | | | 1 | | 1 |
| 15.06.2017 | | | 2 | | 2 |
| 16.06.2017 | | | 3 | | 3 |
| 17.06.2017 | | | 4 | | 4 |
What I want to achieve is that, for example on the 11th of June, I get an average of 14 days and for the 12th I get 3 days.
I suspect I need to do something with the MAX([ManufacturingDate]). In SQL I would try to use an over clause to determine the max per batch number.
What I also want to do is create a filter to group the inventory levels based on ages (0-20 days, 21-40 days, etc.). If I would then select 11.06.2017 in my report, the quantity for Batch 1 would be in the 21-40 days group and the quantity for Batch 2 in the 0-20 days group.
Can anyone help me out?
Regards,
Bart
