Forum Discussion

biloser's avatar
biloser
Regular Visitor
9 years ago
Solved

Calculate with dates

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

Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

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

3 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    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's avatar
      biloser
      Regular Visitor
      9 years ago

      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's avatar
        Phil_Seamark
        Microsoft Employee
        9 years ago

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