Forum Discussion
Helper IIIInventory calculation - Based on expiry date
Hi,
I am trying to create a weekly inventory report, but I am having difficulties creating a measure that adjusts inventory for expiry date. Below are two example tables that show my structure.
tblBatch
Item | Qty |Expiry Date
Alfa 10 05.09.2019
Alfa 25 10.09.2019
Bravo 100 20.09.2019
tblCalendar
Date | Week
01.09.2019 201935
02.09.2019 201936
03.09.2019 201936
[...]
20.09.2019 201938
This I what I am trying to get the output to be like, where inventory is reduced as items pass their expiry date:
rptInventory
Week
Item 201935 201936 201937 201938
Alfa 35 25 0 0
Bravo 100 100 100 0
Any suggestions on how to build the measure would be deeply appreciated.
/depple
I found a solution using the CALCULATE function, with expiry date as filter.
Thanks to all that took their time to read my issue.
/depple
7 Replies
- RobbeVL
Impactful Individual
Hi,
you can simly drag everything to a Matrix Table:
deppleThank you for your suggestion. I have tried this, but it does not return the output that I need. This solutuin plots when which quantity expire, whereas I want the measure to return the non-expired inventory for each week, like in the example I showed in my intial post.
/depple
- RobbeVL
Ok,
Your explenation was not very clear. still isnt.
When is the expiery date? When is the trigger?
