Forum Discussion
Slowly changing dimension type 2
Not sure what to call this but I have a fact table with Category {1...N} and other sized data like Amount and Date etc.
How to I properly work with this kind of dimension table ?
| Category | From | To |
| A | 1.1.2018 | 31.12.2018 |
| B | 1.1.2018 | 31.12.2018 |
| C | 1.1.2018 | 31.12.2018 |
| A | 1.1.2019 | 30.6.2019 |
| A | 1.7.2019 | 31.12.2019 |
| B | 1.1.2019 | 31.12.2019 |
| C | 1.1.2019 | 31.12.2019 |
I want to use Many-to-Many relationship with the fact table and some kind of a DAX measure
Any idea ?
5 Replies
Hi,
What is the actual output you are looking for?
Actual output is a ratio.
I have daily fact data vs resorts with amount of hotel rooms they offer.
Sometimes the amount of rooms changes when they add rooms after reconstructing or something else so Resort A which had 60 rooms to offer now has 75 from 1.st of June 2019 and so forth
The Occupancy ratio is highly depentant on the available rooms : )To make this problem easier to understand, here's my Dimension table
Resort Valid From Valid To Rooms A 1.1.2018 31.12.2018 220 B 1.1.2018 31.12.2018 150 C 1.1.2018 31.12.2018 60 A 1.1.2019 30.6.2019 225 A 1.7.2019 235 B 1.1.2019 160 C 1.1.2019 40 Last column (as a measure ofc ) would be my desired output
Resort Date Nights Desired Output Formula* A 1.1.2018 200 91% 200/220 A 1.1.2019 210 93% 210/225 A 1.8.2019 235 100% 235/235 B 1.1.2018 120 80% 120/150 B 1.1.2019 120 75% 120/160 B 1.8.2019 120 75% 120/160 C 1.1.2018 30 50% 30/60 C 1.1.2019 35 88% 35/40 C 1.8.2019 30 75% 30/40 *just for showcase
