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