Slowly changing dimension type 2

[Yggdrasill]

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 ?

[RobbeVL]

Hi,

What is the actual output you are looking for?

[Yggdrasill]

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 : )

[Yggdrasill]

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

[RobbeVL]

just 1 more question, are this always full montly periods? 

[Yggdrasill]

No, this could be any daily date for that matter.