Forum Discussion
DAX Calculate Inventory Expiry Risk
See if this is what you need:
A temporary measure to get the calculation
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, ABS(_Qty))And to get the total
Expiry Risk Total =
SUMX(Stock_exp, [Expiry Risk Temp])
Sample PBIX file attached
PaulDBrown, unfortunately that doesn't work.
For example the first line. I have 119 pcs, 105 days left. 105 days left * 2.43 = 255 sales possible. I only have 119 pcs. Meaning I can sell all of them before they expire, meaning the risk will be 0 since I can sell all of them.
The risk you calculated is actually higher than the stock I have, which isn't possible because I can't scrap more items than I actually have.
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?