Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
depple
Helper III
Helper III

Inventory 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

1 ACCEPTED 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

View solution in original post

7 REPLIES 7
RobbeVL
Impactful Individual
Impactful Individual

Hi,

 

you can simly drag everything to a Matrix Table:

 

Capture.JPG

@RobbeVL 

 

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 

RobbeVL
Impactful Individual
Impactful Individual

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

Anonymous
Not applicable

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

 

Anonymous
Not applicable

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]))

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.