Projecting Inventory to Expire based on forecast

[JCPO]

Hi everyone!

Posting this question for my inventory tracking project. 

Summary: I would like to build a tracker/dashboard to track my inventory expiration risks based on forecasted usage.

Inputs:

1. I have one table for forecasted usage on a weekly basis for all SKUs. Similar to the table below.

2. On a separate table, I have an inventory balance of all of these SKUs, with different batch codes and different expiration dates similar to the table below. 

That being said, my desired outputs are: 

1. Flag expired items automatically (can be easily done). 

2. Be flagged if there is an expiration risk meaning, total usage over X weeks will not be enough to deplete the remaining inventory by its expiration date. Need to keep in mind that First to Expire, First to consume. I need to deplete first those to expire first before consuming the next batch. 

3. Quantify the risk: How much will not be consumed/will expire based on forecast usage. Keeping in min the First to Expire, First to Consume concept. 

Sample output table:

Hope I made the problem clear. 

I'm really stuck at this project and don't know where to start so I would very much appreciate any help.

Thank you!!!

[solid_shnake]

I have an identical problem, has anyone been able to resolve?

[Anonymous]

dear @Sdhn420  i have still no resolution on this topic 😞 unfortunately

[Sdhn420]

Hi all, any luck with this solution?

[Sdhn420]

@JCPO did you get a solution for this? I am also stuck with the same issue.

[Anonymous]

hi, I have EXACTELY the same issue. I was wandering if u found a soultion to your project? thx!!

[Greg_Deckler]

First, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Second, I think you are looking for something along the lines of Days of Supply:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Days-of-Supply/m-p/635656#M318

[JCPO]

Hi @Greg_Deckler ,

I'm valuating the opposite which is excess supply. 

I'm stuck with the problem considering that:

1. Different batches and different expiration dates per material,

2. I need to factor in First to Expire, First to Use concept. 

A straightforward calculation of Total Usage - Inventory will not work for the project. 😞

[v-kelly-msft]

Hi @JCPO ，

Could you show us your .pbix file or sample data if there's nothing confidential?You can upload it to onedrive and share us with the link.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[JCPO]

Hi @v-kelly-msft , 

Here's the sample file for your reference

https://1drv.ms/x/s!Al4XUPsgrRwrzEfkjaedUIEPpWql?e=7m8TsP

Thanks!

JCPO

[v-kelly-msft]

Hi @JCPO ，

So for "status":

IF('Table'[date]>'Table'[Expiration date]&&'Table'[On hand Inventory]>0,"overstock",BLANK())

What date should I use to compare with 'expiration date'?Today()?

For"Inventory stock",how should I get the result?

Your sample data is really a bit complex,better show me a clear logic.

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

[Anonymous]

hi, since @JCPO ist not active I will try to put some logic. 

Expiration date should pe a parameter until when the goods can be used. 

In case that FC qty. until the expiration day is lower than stock on hand, you will end up with excessive stock that has to be evaluated. In this case from the day of expiration you need to switch to the next exp. date batch of the same product if existing and continue to supply according to the forecast. 

In the opposite case where FC qty. ist higher than qty. expirarion day simply switch to next batch if available when stock ist utilised. 

Hope it helps. br Mila

[Sdhn420]

@Anonymous thank you so much for your response.

Your logic makes sense. The next challenging part is how will the dax be around this logic?