Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, I want to calculate the inventory per month.
I have 3 tables:
1)Warehouses master data (ID /State /City)
WarehouseID | STATE | CITY |
CHIST000030 | CHIHUAHUA | CHIHUAHUA |
BCIST000011 | BAJA CALIFORNIA NORTE | ENSENADA |
ASIST000016 | AGUASCALIENTES | AGUASCALIENTES |
2)Warehouse with Qty per material purchases (IN) and consumption (OUT)
WarehouseID | Date | ItemID | PURCHASE | CONSUMPTION |
CHIST000030 | 05-ene-23 | ABC1 | 10 | 1 |
CHIST000030 | 31-ene-23 | ABC2 | 15 | 3 |
CHIST000030 | 31-ene-23 | ABC1 | 20 | 3 |
CHIST000030 | 01-feb-23 | ABC2 | 30 | 3 |
BCIST000011 | 01-feb-23 | ABC1 | 15 | 4 |
BCIST000011 | 01-feb-23 | ABC2 | - | 3 |
BCIST000011 | 01-feb-23 | ABC1 | 10 | 3 |
CHIST000030 | 01-feb-23 | ABC2 | - | 3 |
ASIST000016 | 28-feb-23 | ABC1 | 9 | 3 |
ASIST000016 | 28-feb-23 | ABC2 | 28 | 5 |
BCIST000011 | 01-mar-23 | ABC1 | - | 2 |
CHIST000030 | 06-mar-23 | ABC2 | 5 | 3 |
ASIST000016 | 06-mar-23 | ABC1 | 12 | - |
ASIST000016 | 06-mar-23 | ABC2 | 12 | 15 |
CHIST000030 | 06-mar-23 | ABC1 | 32 | - |
CHIST000030 | 06-mar-23 | ABC2 | 90 | - |
CHIST000030 | 06-mar-23 | ABC1 | - | 3 |
CHIST000030 | 06-mar-23 | ABC2 | 3 | - |
CHIST000030 | 06-mar-23 | ABC1 | - | 3 |
CHIST000030 | 06-mar-23 | ABC2 | 20 | 3 |
CHIST000030 | 30-mar-23 | ABC1 | - | 15 |
CHIST000030 | 30-mar-23 | ABC2 | - | 3 |
3)Dates.
Date |
01-ene-23 |
02-ene-23 |
03-ene-23 |
04-ene-23 |
05-ene-23 |
06-ene-23 |
07-ene-23 |
08-ene-23 |
09-ene-23 |
10-ene-23 |
11-ene-23 |
… |
31-dic-23 |
Here's the desired result:
Thank You!!
@CGarciaV , You need to use running total with help from date table joined to date of your table
CALCULATE(SUM(Table[PURCHASE]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[CONSUMPTION]),filter(date,date[date] <=maxx(date,date[date])))
Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
95 | |
94 | |
79 | |
71 | |
64 |
User | Count |
---|---|
120 | |
105 | |
99 | |
81 | |
72 |