Calculate with dates

[biloser]

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.

1. For each date of a month I want to know and visualize the sum of the customers that stayed in the hotel (for example in a matrix) and maybe filterable by room category. Would be great if also a time slicer can be used, so that the number of customers could also be given by month.

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? 

1. If I know the Amount of customers per day, I could also calculate the proceeds per day by calculating the customer amount*price per night of the room category. I think I would be able to solve it by myself but therefore I need a solution for the first step L

Maybe someone could help me?  I would be very happy!

Thank you in advance!

Best Regards

Biloser

[Phil_Seamark]

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

[biloser]

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

[Phil_Seamark · Accepted 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]
                )