cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Yggdrasill Member
Member

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 ?

CategoryFromTo
A1.1.201831.12.2018
B1.1.201831.12.2018
C1.1.201831.12.2018
A1.1.201930.6.2019
A1.7.201931.12.2019
B1.1.201931.12.2019
C1.1.201931.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 5
RobbeVL Established Member
Established Member

Re: Double Trouble Dimension

Hi,

 

What is the actual output you are looking for?

 

Yggdrasill Member
Member

Re: Double Trouble Dimension

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 Member
Member

Re: Double Trouble Dimension

To make this problem easier to understand, here's my Dimension table

ResortValid FromValid ToRooms
A1.1.201831.12.2018220
B1.1.201831.12.2018150
C1.1.201831.12.201860
A1.1.201930.6.2019225
A1.7.2019 235
B1.1.2019 160
C1.1.2019 40

 

Last column (as a measure ofc ) would be my desired output

ResortDateNightsDesired OutputFormula* 
A1.1.201820091%200/220
A1.1.201921093%210/225
A1.8.2019235100%235/235
B1.1.201812080%120/150
B1.1.201912075%120/160
B1.8.201912075%120/160
C1.1.20183050%30/60
C1.1.20193588%35/40
C1.8.20193075%30/40

*just for showcase

RobbeVL Established Member
Established Member

Re: Double Trouble Dimension

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

Yggdrasill Member
Member

Re: Double Trouble Dimension

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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,748)