Hi guys,
I've been trying to tackle this problem for a few days now and can't seem to wrap my head around it. I tried to provide examples of all the tables I'm working with.
Setup:
Contract (Fact table), which tell how many and which contracts are valid on certain date and which and their product ID (there are multiple types of contracts/products).
| Unique contract ID | Start date (dd.mm.yyyy) | End date (dd.mm.yyyy) | Product id | Inventory type |
| 1 | 1.7.2023 | 1.7.2023 | 15 | 1 |
| 2 | 1.6.2023 | 31.12.2023 | 12 | 2 |
| 3 | 1.5.2023 | 31.9.2023 | 15 | 1 |
These contracts, while they are valid, consume inventory. The inventory is filled every once in a while and that info is received from another table (inventoryfills_fact)
| Date | Amount of units | Price per unit ($) | Inventory type |
| 10.5.2023 | 250 | 15 | 1 |
| 5.6.2023 | 100 | 12,5 | 2 |
The amount of consumption is received from another table (consumption_dim)
| Product ID | Consumption per month | consumption per day (month / 30 ) | Inventory type | consume price ($) |
| 12 | 60 | 2 | 2 | 20 |
| 15 | 30 | 3 | 1 | 18 |
This is a dimension table
I also have a basic calendar table with dates, year, month number etc that is connected to to both inventoryfills and Contract with one to many-relationship.
Desired outcome:
I would need information of:
- Amount contracts active and the cumulative history
- Current inventory level in units and its' cumulative total over time, which is (inventory purchases - consumption) (this is grouped monthly as consumption always takes place for the whole month even if contract is not valid for a full month). In the end this would be divided by inventory types as well (1 or 2)
- Total sales amount per month ('consumtion_dim'[consume price] * number of contracts valid each month)
- Total consumption per month ('consumtion_dim'[consumption per month] * number of contracts valid each month)
Currently I have cumulative contract amount with:
Cumulative contract amount = CALCULATE(
COUNTX(
FILTER('Contract', 'Contract'[start_date] <= MAX( calendar[Date]) && 'Contract'[end_date] > max(calendar[Date])),
'Contract'[Contract_ID]), CROSSFILTER(calendar[Date], 'Contract'[start_date],None))
And the cumulative sum of inventory fillings with:
Inventoryfills_CumulativeSum =
CALCULATE(SUM(inventoryfills_fact[Amount of units]),
FILTER(ALL(Kalenteri),
Kalenteri[Date] <= MAX(Kalenteri[Date])
)
)
But I can't seem to be able to connect inventory levels correctly with the amount of contracts and their unique consumption.
Open for any ideas you guys might have.
Example of Desired outcome:
| Year - month | Consumption | inventory | inventory type | contract amount | contract revenue |
| 2023 - 04 | 0 | 0 | | 0 | 0 |
| 2023 - 05 | 30 | 220 ( 250 - 30 ) | 1 | 1 | 20 |
| 2023 - 06 | 30 | 190 ( 160 -30 ) | 1 | 1 | 20 |
| 2023 - 06 | 60 | 40 ( 100- 60 ) | 2 | 1 | 18 |
| 2023 - 07 | 60 | 160 | 1 | 2 | 40 |
| 2023 - 07 | 60 | - 20 | 2 | 1 | 18 |
Cheers!
