Help: Occupancy rate

Hi Anonymous 

I've had a look at this and have attached a PBIX with some suggestions.

The basic measure you're wanting is an "events in progress" type measure.

Generally speaking, you can either

1. Have a table with one row per session, with columns for start and end datetime values
2. Transform your table so that there is one row for every datetime value for which a session is ongoing (at some time granularity, e.g. hourly

In my sample file I have used both methods. Here is what I did:

1. For Option #1, Created a ChargingData table with dummy data looking like this (I created a random dataset with a DAX calculated table):

    

2. Created a Date table covering the range of dates in ChargingData
3. Created a Time table with hourly granularity. Each row really corresponds to an hourly interval:

    

4. Created an Occupancy measure
   

   Occupancy = 
   VAR MinDateTime =
       MIN ( 'Date'[Date] ) + MIN ( 'Time'[Time] )
   VAR MaxDateTime =
       MAX ( 'Date'[Date] ) + MAX ( 'Time'[Time End] )
   RETURN
       CALCULATE ( 
           DISTINCTCOUNT ( ChargingData[Session ID] ), // Could also use COUNTROWS ( ChargingData )
           ChargingData[Start date] < MaxDateTime,
           ChargingData[End date] >= MinDateTime
       )

   Note that there is no relationship between Occupancy and either Date or Time.
   This measure counts the number of distinct sessions that occur during the selected date/time interval.
5. For Option #2, created a ChargingDataReshaped table. This table contains one row for each Session ID & hourly interval during which that Session ID is active. The DAX code for this table is in the PBIX.
   Note that this table is related to Date & Time
6. Created a measure Occupancy v2 which is simply:
   

   Occupancy v2 = 
   DISTINCTCOUNT ( CharginDataReshaped[Session ID] )

7. Now both Occupany & Occupancy v2 give the same results when filtered by Date & Time. I also created a Charge ID table that filters both ChargingData & ChargingDataReshaped.
8. Now the Occupancy measures can be visualized however you want, e.g.

    

I would expect Option #2 to perform better in general. However, you may want to keep both versions of the data in the model for different purposes.

Hopefully that's of some use and can be adapted as needed.

Regards,

Owen