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