Sum of filtered distinctcounts

[tachydidaxy]

I have two tables in my current model; a fact table with hotel reservations and a lookup table for the hotel's rooms. I have been asked to generate a report showing room occupancy rates, but for weekends only.

The reservations table has a reservation_id, room_id, check_in_date and check_out_date fields which I've used to succesfully tally up the occupancy rates with the following measure (the way I've constructed the measure required that I don't have a relationship with the calendar table as far as I understand):

 Rooms Occupied:=
CALCULATE (
    DISTINCTCOUNT ( Reservations[room_id] ),
    FILTER ( Reservations, Reservations[reservation_status] <> "OS" ),
    FILTER ( Reservations, Reservations[room_id] <= 40 ),
    FILTER (
        Reservations,
        Reservations[check_in_date] <= LASTDATE ( Calendar[dates] )
            && Reservations[check_in_date]
                + ( Reservations[days_of_stay] - 1 )
                >= FIRSTDATE ( Calendar[dates] )
    )
)

 This gives me expected results when dropped in a PivotTable as shown below:

As you can see I've filtered to only show the weekends (defined as Friday and Saturday for our purposes), I've also used the HASONEVALUE() to not show the Room Occupancy measure for Months and Years which is my problem. 

The problem is that I'm not getting a proper total for the Month and Years for the Room Occupancy measure. I understand that since I'm trying to sum up distinctcounts of room ids (of which there are only 40) my totals for months and years always only adds up to 40 instead of a proper sum. For instance in the screen grab below for the month of January, I'd be looking to have a total of 151 for Jan.

But instead I get only 40 as shown below:

Any suggestions as to the best way to address?

[tachydidaxy]

I somewhat understand why this problem is happening, I've used a DISTINCTCOUNT(Reservations[room_id]), so the total can never be higher than the total number of available rooms.

My thoughts on dealing with this issue are that I'd have to test first to see if Calendar[dates] HASONEVALUE(), and if it does, then return the measure I've already created which works at the lowest level of granularity.

However, where I stumble is how to address the rows in which I dont' have a single value for Calendar[dates] which are namely the Months (Jan, Feb, etc.) and Years (2017). For these rows I'd need to somehow iterate through each of the individual days available in the current report's context and then sum those up.

I'd imagine the forumla would look something like this logically:

IF (current row's calendar context) IS a single day THEN (return the measure I already have that works on day level of granularity) ELSE (return a new corrected measure which iterates through each day available within the current row's context using the measure I already have that works on a day level of granularity and then sums those values for each day together)

Or (assuming the measure I know already works for the day granularity is named [Room Occupancy for Day])

Correct Rooms Occupied Measure :=
IF (
    HASONEVALUE ( Calendar[dates] ),
    [Room Occupancy for Day],
    CALCULATE ( SUMX ( VALUES ( Calendar[dates] ), [Room Occupancy for Day] ) )
)

Any guidance will be appreciated.

[bdymit]

Why not change 

 Rooms Occupied:=
CALCULATE (
    DISTINCTCOUNT ( Reservations[room_id] ),

to

 Rooms Occupied:=
CALCULATE (
    COUNT ( Reservations[room_id] ),

Theoretically, you would not be able to reserve the same room in one day, right? So is Distinct Count really necessary?

Let me know if I am missing something.

Thanks,

Ben 

[tachydidaxy · Accepted solution]

---

Thanks Ben. I appreciate your time. And you are correct, switching from a DISTINCTCOUNT to a simple count did work for my needs of counting the occupied rooms on the lowest level of granularity (Calendar[dates]). And got me much closer to my goal of having a total for Months and Years.

In my defense, I initally used DISTINCTCOUNT() because each reservation has a date range, from Reservation[check_in_date] to Reservation[check_out_date]. So there actually can be two reservations for the same room on the same day as check out is 12pm and check in is 2pm. That's why I was initally using the DISTINCTCOUNT() of the Reservation[room_id] field instead of the Reservation[reservation_id] field.

However, something was still off with my aggregated Month (Calendar[Month]) and Year (Calendar[Year]) totals. See screen grab below for reference.

I verified the totals for the days in August are where they are supposed to be, but looking at the total for the Month of August, the total count of the highlighted values is 755, instead it's only showing as 408 in my pivot table. Similarly, you can see the "Rooms Out of Service" and "Rooms Not Available" columns are not aggregating properly either.

I was able to get this to work correctly however with a little bit of google-fu and luck.

The key for me was this post which showed me how to use SUMX() with an already created measure in order to get proper aggregations over Month and Years.

So, for instance, in order to get the working "Rooms Occupied" column you see in the above screen grab for the report that was correct. I had to first create a measure that tallied up the occupied rooms on the day level (i.e. for Calendar[dates]) which is shown below:

Occupied Count :=
CALCULATE (
    COUNT ( Reservations[room_id] ),
    FILTER (
        Reservations,
        Reservations[reservation_status] = "I"
            || Reservations[reservation_status] = "O"
    ),
    FILTER ( Reservations, Reservations[room_id] <= 40 ),
    FILTER (
        Reservations,
        Reservations[check_in_date] <= LASTDATE ( Calendar[dates] )
            && Reservations[check_in_date]
                + ( Reservations[days_of_stay] - 1 )
                >= FIRSTDATE ( Calendar[dates] )
    )
)

I then created another measure, the measure I use in my actual report/pivot-table which uses SUMX to tally up the rooms occupied for each day currently available in the pivot table's period which is as follows:

Rooms Occupied:=SUMX(DISTINCT(Calendar[dates]),[Occupied Count] )

I used the same process for  Occupancy Rate, Rooms Available (Total for Selected Dates) and other measures. Is a little slow when updating the pivot because it's iterating mutliple times through the fact table, but otherwise works as intended.

Thanks again for help and time.