Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
For a hotel I have the check in and check out dates of its customers as well as the room category (A,B,C) and its cost per night, as you can see in the pictures as well as in the attached power bi file (https://drive.google.com/open?id=0BwAkqQj_w6nfS01UMmRHdTVWOE0). I also have a DateDimension Table.
I want to calculate and visualize the following two things but I don’t know how to realize it.
To give an example, if the data would be the following:
check in date | check out date |
01.01.2017 | 02.01.2017 |
01.01.2017 | 05.01.2017 |
02.01.2017 | 06.01.2017 |
03.01.2017 | 05.01.2017 |
Then the amount of customer for the first 3 days of a month should be counted like that. As the guest leave the room at the check out date, it doesn’t count:
|
|
|
|
| |||
01.01.2017 | 02.01.2017 | 03.01.2017 |
| ||||
Amount of customers | 2 | 2 | 3 |
| |||
What Measure do I need to realize it?
Maybe someone could help me? I would be very happy!
Thank you in advance!
Best Regards
Biloser
Solved! Go to Solution.
Hi @biloser
I was wondering if you wanted that day counted or not.
Here is the modified measure that doesn't count the check-out date
New Table = FILTER( CROSSJOIN( 'Check IN and Checkout Data',CALENDARAUTO()), [Date]>='Check IN and Checkout Data'[Check in Date] && [Date] < 'Check IN and Checkout Data'[Checkout Date] )
Hi @biloser
This might work. I created a new calculated table as follows:
New Table = FILTER( CROSSJOIN( 'Check IN and Checkout Data',CALENDARAUTO()), [Date]>='Check IN and Checkout Data'[Check in Date] && [Date]<= 'Check IN and Checkout Data'[Checkout Date] )
and then created a calculated measure on the new table
Customer Count = CALCULATE(DISTINCTCOUNT('New Table'[Customer Number]))
which you can use in a matrix
Hey,
thank you very much! I searched for this solution!
I just have one question: I guess the new created table also include the checkout date. Is it possible to ensure that the checkout isn't count?
Like if the check in date is 01.01. and the checkout date is 03.01. , it creates a row for 01.01. and 02.01 but not for 03.01.?
Thanks and Best Regards,
Biloser
Hi @biloser
I was wondering if you wanted that day counted or not.
Here is the modified measure that doesn't count the check-out date
New Table = FILTER( CROSSJOIN( 'Check IN and Checkout Data',CALENDARAUTO()), [Date]>='Check IN and Checkout Data'[Check in Date] && [Date] < 'Check IN and Checkout Data'[Checkout Date] )