Daily Occupancy Average

[mpmsltd]

Hi everybody,

I have a table that shows the Start Date, End Date and Customer ID. What I want to do is to see the daily occupancy average per period of time (year for example and be able to drill down to a month). Another requirement is the ability to see on each day how many occupants there were exactly (not sure if that would be on a different visual). 

For example:

for 11/07/2018- I would have everybody that started on this date minus people that left on this day plus people that have arrived earlier but either checked out later or haven't checked out yet (end date = blank).

This is pretty much a sample of what I have where num is the customer ID. Like I mentioned before the blank end date should be considered as not ended yet.

Can somebody please help me out? Thank you so much in advance!

num	start date	end date
700	12/17/2015	8/11/2016 8:18
1096	12/31/2015	12/31/2015 21:27
1095	12/30/2015	12/30/2015 16:59
920	12/15/2015	12/15/2015 15:31
1080	12/14/2015	12/14/2015 4:39
1077	12/9/2015	12/9/2015 13:21
1078	12/9/2015	12/9/2015 2:52
904	8/26/2015	8/26/2015 12:01
897	12/6/2015	12/6/2015 4:44
1074	12/2/2015	12/2/2015 3:08

[Anonymous]

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

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file and show the expected result.

[Anonymous]

First of all, thank you all for helping us. 

THis is the link where you can download the pbix. file:

https://1drv.ms/u/s!AtEu70FghQZohcZeLVDKCRAsVeh3Xg?e=WbXSad  

Now, there you will see my comments and the expected results there. 

We are having trouble since the total values for the measure is not computing the sum of rooms occupied per night. So no clue on getting a monthly avarage occupancy rate...

Thanks so much. 

Santiago Sanchez

[Ashish_Mathur]

I do not understand anything in that file.  First the language is not English.  Second, how do you say that the answer should be 49?????  There are just way too many visuals there.  Delete the ones that are not required so that i know what i have to focus on.

[Anonymous]

I apologize for the inconvenience @Ashish_Mathur. This is the new file, where I give more detailed information and eliminate what is not necessary to understand the system and the measures I'm trying to apply.

https://1drv.ms/u/s!AoHoPnkGYJRRb5miDOfd3QT_q94?e=58YbIg

Thanks for your kind support

Santiago Sanchez

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[mpmsltd]

Thanks @Ashish_Mathur  but this is not what I need. 

What I need is the daily occupancy average - which is a calculation of: customers that were staying on that day + customers that arrived on that day - customers that left on that day

[Ashish_Mathur]

Hi,

You are welcome.  Show me your exact expected result.

[mpmsltd]

hi @Ash in yellow is my expected result, the daily occupancy by day. Plus I'd like to be able to see the daily average occupancy in a month as well. The column "counted on dates" shows which dates the id is being counted as occupant.

Please let me know if any questions. Thanks

[Ashish_Mathur · Accepted solution]

Hi,

You may download my solution file from here.

Hope this helps.

[mpmsltd]

@Ashish_Mathur sorry but again you are just counting the number of ids, this is not what I'm looking for. Please see my example previously showing my expected result, it considers the start and end date to define the daily occupancy.

[Ashish_Mathur]

Hi,

That is exactly what I am doing.  My answers matches what you have shown in the yeloow highlighted section of your previous message.

[mpmsltd]

In the sample file that you uploaded it does not have a start and end date, there's only one date on the Data table. Please check, my example has two different columns, start and end date.

[Ashish_Mathur]

Hi,

I have treated 3 columns as inputs and have then run a transformation to convert the 2 date columns into a single date column.  This was done to create a single date slicer.

[mpmsltd]

Hi @Ashish_Mathur  wow! Sorry about that I was searching for a crazy measure or calculated column without going to the Edit Query mode, but it seems that your solution is more simple and efficient! According to my tests it's working with my data, thank you so much.

[Ashish_Mathur]

You are welcome.

[v-frfei-msft]

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.

[mpmsltd]

Hi @v-frfei-msft thank you so much for your help.

But the totals are off, please check the file attached. For example the total for January 2017 is showing 586 but then all the days have more than 700 people. Can you please check it again? Also how can I attach my pbix file in here?

 THanks!