Forum Discussion
Calculate inventory per month
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!!
1 Reply
- amitchandak
Super User
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
