Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
Hi,
you can simly drag everything to a Matrix Table:
Thank 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
Ok,
Your explenation was not very clear. still isnt.
When is the expiery date? When is the trigger?
Sorry if I am being unclear. I will try to correct this.
This is the desired output of the measure given the data in the two tables I provided in the initial post:
Week
Item 201935 201936 201937 201938
Alfa 35 25 0 0
Bravo 100 100 100 0
For each future week, non-expired batches are summed. For the week that a batch has the expiry date, it will not be included in the inventory, for example the 10 Alfa that has expiry date 5/9-2019 (Week 201936) are not included in Week 201936, as they expire that week. The remaining 25 Alfa expire in 10/9-19, and therefore from 201937 and forwards, there a 0 Alfa in inventory.
Again, sorry for being unclear. I hope that this is a better explanation.
/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
Hello depple,
Trust you are great. Please can you help share the DAX measure you used to solve this problem? I have a similar issue I am currently on.
I want to show the cumulative inventory level considering the future expiry dates.
Thanks
deji
hi depple,
can you give us the example where you use the filter?
i have also tried to do the same with calculate (distinctcount(field1),filter(allselected(table), [expirydate] >= [i still havent figured out what i should put in here]))
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |