Help: Occupancy rate

[Anonymous]

Dear community,

I'm having trouble with converting existing excel files into interactive power bi reports.
The part I'm currently stuck at is concerning the occupancy rate for charging stations. My data is confidential but I'll try and provide a basic example:

Basicly my fact table is as follows:

Sessions ID(unique for each session)
Start date(1-1-2019 13:15:00) formated like this example
End date(1-1-2019 17:22:30)
Duration(04:07:30) in this example, looking at start and end date/time
Volume: 3,0 kWh.
Charge ID: 101( ID's range from 100 to 500, and each station has 2 sockets so overlapping start and end date is possible, I dont have any data on wether socket 1 or 2 is in use( just to make things extra diffcult... 😞 ))

I tried construcing a list for each hour for the earliest to latest dates I have in my date, but couldnt succeed.
I think that after I have this table, I can somehow construct a variable/column or measure that can give me a 0 or 1 for when a charge station is occupied and calculate occupancy based on that, is that correct?

Looking forward to al the eloquent solutions.
I tried following this thread, got idea's from there so all kudo's should go to the respective people:
https://community.powerbi.com/t5/Desktop/Calculating-hourly-occupancy-of-a-medical-clinic-based-on-a...

Kind regards,

Binair

[Stachu]

how do you want to count the occupancy exactly? the way I understand it, it will change depending to what time frame you compare it, is that correct? E.g. looking at your example:

Sessions ID	Start date	End date	Duration	Volume	Charge ID
1	01/01/2019 13:15	01/01/2019 17:22	04:07:30	3	101

if I calculate the occupancy for 01/01/2019 it will be ~4h/24h, but if I calculate it for 01/01/2019-02/01/2019 it will be ~4h/48h, correct? if that's the case then you definitely need to use a measure, not a column

what's the time span that you look at? days, weeks, years? can you also provide some more rows with more tricky examples e.g. multiple start/end dates - can they overlap or not? if they can overlap does it count as 100% occupancy at this point in time or 200%? in the copiable format, as I posted above

[Anonymous]

Hello Stachu,

I'm sorry for not specifying that beforehand. The occupancy rate I want in the end is per hour, like @OwenAuger did in his solution.
Because like you said, sessions can overlap with each other, and when they do, the occupancy of the station is 100%, since it has 2 sockets only.
Thank you for thinking  and helping with a solution!

[OwenAuger · Accepted solution]

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

[Anonymous]

Hello @OwenAuger ,

Thank you for this eloquent solution!
I shall try and reenact your steps and apply these two options to the original data.
I really appreciate it you took the time and effort! A true datanaut!
Can I converse further via pm if I have any further questions about reenacting your steps?

Kind regards,

Binair 

[OwenAuger]

No problem, yes feel free to PM but also feel fre to post further replies here as I will see the notification.

Regards,

Owen