Hello everyone! I've long researched for a solution for this problem and never found it... maybe someone can help me solve this once and for all 🙏
I want to know how can I create a measure that sums units in inventory, but also considering the max(Date) avaliable in any given context.
Below is the dummy data table, with:
- A list of brands (A, B and C)
- Dates in which their is any stock data recorded for this brands (some with daily data ,some with a lot of data gaps)
- The storage facility in with that stock is stored (Some brands have only one, others more)
- The product ID
- And the metric, the units of stock of that product in that facility, on that date given, for its respective brand.
- If I create a measure in PowerBI like SUM(Units), it will show incorrect data if I want to show more than one date at a time. It would only work if my STOCK table had only the last "snapshot" of inventory, but I need to be able to see how stock was on previous periods as well.
- If I create a measure that calculates the SUM of units for the last date available (see DAX code below), then it doesn't sum up correctly when I add breakdowns. Why? because I don't have data for all products, on all storage facilities, and all the calendar dates possible. So when one brand has a more updated inventory_date, it only shows the SUM for that brand.
CALCULATE(SUM('- STOCK'[units_in_stock]),'- STOCK'[Inventory_Date]=MAX('- STOCK'[Inventory_Date]))
Is the only solution available, to create all "nulls" possible for all my calendar dates, posible skus and storage facilities, or is there another way around?
Thank you in advance!
Marina
Sample data:
| brand | Inventory_Date | storage_facility | Product_ID | Units in stock |
| A | 2/5/2023 | Own | 112233 | 229 |
| A | 2/5/2023 | FBA | 112233 | 273 |
| A | 3/5/2023 | Own | 112233 | 228 |
| A | 3/5/2023 | FBA | 112233 | 350 |
| A | 4/5/2023 | Own | 112233 | 228 |
| A | 4/5/2023 | FBA | 112233 | 342 |
| B | 29/4/2023 | Main | 112244 | 811 |
| B | 29/4/2023 | Main | 334455 | 1335 |
| B | 30/4/2023 | Main | 112244 | 801 |
| B | 30/4/2023 | Main | 334455 | 1234 |
| B | 1/3/2023 | Main | 112244 | 742 |
| B | 1/3/2023 | Main | 334455 | 1233 |
| B | 8/3/2023 | Main | 112244 | 1005 |
| B | 8/3/2023 | Main | 334455 | 1231 |
| B | 15/3/2023 | Main | 112244 | 1002 |
| B | 15/3/2023 | Main | 334455 | 1220 |
| C | 22/3/2023 | FBM | 333333 | 1413 |
| C | 29/3/2023 | FBM | 333333 | 1213 |
| C | 5/4/2023 | FBM | 333333 | 902 |
| C | 1/3/2023 | One | 222222 | 2234 |
| C | 22/3/2023 | One | 222222 | 1384 |
| C | 5/4/2023 | One | 222222 | 861 |
