Daily Occupancy Average

Hi mpmsltd ,

Please check the following steps as below:

1. Create the inactive  relationship between tables as below.

2. Then we can create measures as below to work it out.

Measure = 
VAR mind =
    MIN ( 'date'[Date] )
VAR mad =
    MAX ( 'date'[Date] )
VAR a =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            mind >= 'Table'[start date]
                && OR ( 'Table'[end date] >= mad, 'Table'[end date] = BLANK () )
        )
    )
RETURN
    a

Measure 2 = 
VAR d =
    MAX ( 'date'[Date] )
VAR a =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        USERELATIONSHIP ( 'date'[Date], 'Table'[start date] )
    )
VAR b =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        USERELATIONSHIP ( 'date'[Date], 'Table'[end date] )
    )
VAR c =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            'Table',
            'Table'[start date] > d
                && OR ( 'Table'[end date] = BLANK (), 'Table'[end date] > d )
        )
    )
RETURN
    a - b + c

For more details, please check the pbix as attached.

Hi,

You may download my PBI file from here.

Hope this helps.

Hello All,

Thanks for your replies. Ashish_Mathur ´s solution has helped us a lot. 

Now, we want to take it to the next level and evaluate this measure for a MONTH scale or even within a YEARLY basis. 

This is not being possible since the total OCCUPIED ROOMS are not a sum of the occupied rooms for every night. I believe this was also the case for posted solution, since the total does not match up with the correct sum for the period of nights shown. 

Any ideas how we can manage this? 

In case you need to see toher tables from our model, let me know. 

Thanks in advance!!

Santiago Sanchez 

P.S: In our model, every reservation code has a defined number of occupied rooms (the values are calculated also using a TABLE created within POWERQUERY ).