Forum Discussion
DAX Calculate Inventory Expiry Risk
Apologies, you are absolutely right. I was calculating the "extra sales" as opposed to the Expiry Quantity.
Try this for the temp measure:
Expiry Risk Temp =
VAR _Days = DATEDIFF(TODAY(),MAX(Stock_exp[Date]), DAY)
VAR _Sales = _Days * 2.43
VAR _Qty = SUM(Stock_exp[Stock]) - _Sales
RETURN
IF(_Qty > 0, _Qty)
PaulDBrown, unfortunately that's also incorrect.
Till 31 May = 169 days * 2.43 = 410 pcs of sales
I have 119 + 121 + 806 = 1046 - 410 = 636 pcs with exp risk and not 395.
You calculate it per line, but the calculations needs to take the results from all previous lines into account as well.
So what determines the cumulative value? The absolute chronological order or the sequence order? If it's the sequence order, what is the calculation for example for sequence 1->4?
PaulDBrown, the cumulative value is dictated by the sequence in which the locations will be empties, aka the Sequence column.
Just image you have 8 locations in front of you, all have the same item. Each location has an expiration date and you start selling from the most left locations and work your way to the right. You might encounter a location with stock which you can't sell anymore because you have reached the expiry date and you need to scrap those items and move to the next location.
Regarding your question regarding the 1 > 4 sequence.
Location 1 has 119 pcs. Till 28.03 I can sell 250pcs, meaning I can sell all items before this date is reached, actually I could sell even (250 - 119=) 131 more pcs.
Then I start from location 2. Here are 121 pcs. I can sell all of these even before the exp date from location 1 is reached. Till this point in time I could have sold 406 pcs ((31.05 - today ) * 2.43). I've only sold 240 (191 + 121) so far.
Then I move to location 3. Same date as locaiton so meaning I can still sell 166 (406 - 240) from this location. However I have 806 on this location meaning I need to scrap 640 pcs (806-166).
Then I move to location 4. All previous locations have been empties by selling or expiry. So from the exp date of location 3 till the exp date of location 4 I can sell (30 * 2.43=) 72 pcs. I only have 42 so I can sell all of them before the exp date is reached, I could even sell (72 - 42 =)30 more.
Location 5&6&7. These dates are before the date of location 3 for which I scrapped the pcs I couldn't sell, so I need to scrap all of these as well.
Location 8 is the same as location 4. I was still able to sell 30 more before this date was reached so I will sell those 30 and for the rest there is no time left since the exp date will be reached before I can sell them. Meaning here I have to scrap 928 - 30 pcs meaning I will have to scrap 897pcs on this location.
Hello, I am trying to do the same, just simple 1 location for now.
Inventory - expiry date, calculate what my risk is going to be, based on expected sales.
Did you ever get a solution for this ?
