Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi all,
I am working on a measure to present the closing balance of inventory on SKU level
basica logic is SOH-residual balance of forecast+inbounds on monthly basis:
The DAX i had give me below result
It should be
SOH_Full | Inbounds | Aactual | Current FC | ATP | Formula | |
Sep-24 | 8331 | 1107 | 1990 | 7448 | =B2+C2+IF(E2<D2,0,E2-D2) | |
Oct-24 | 1560 | 5888 | =F3-E2+C2 | |||
Nov-24 | 1883 | 4005 | =F3-E4+C4 | |||
Dec-24 | 2337 | 1668 | =F4-E5+C5 | |||
Jan-25 | 1567 | 101 | ||||
Feb-25 | 6000 | 1371 | 4730 | |||
Mar-25 | 2010 | 2720 | ||||
Apr-25 | 2083 | 637 | ||||
May-25 | 1707 | -1070 | ||||
Jun-25 | 1675 | -2745 | ||||
Jul-25 | 1608 | -4353 | ||||
Aug-25 | 2334 | -6687 | ||||
Sep-25 | 2062 | -8749 |
In addition, as above is on SKU level, I also need a view of product family level which means sum up all postive numbers/zero out all negative month over month.
Highly appreciate if your help in advance.
Chen
Hi @cyuan ,
My concern with summing only the positive values and zeroing out the negative ones is that it will consistently overstate the inventory in the model.
Best regards,
Hi ninja,
thanks reply. in my case forecast will expire month over month, hence no backorder qty should be counted in, once inventory balance is negative or zero, new month forecast should also be zero out too because nothing we could supply such that inventory will not be inflated.
hope it makes sense.
CY
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
31 | |
16 | |
14 | |
14 | |
9 |