cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

Car Park occupancy per hour

Hi guys,

I have read the other posts on the "Car Park Topic" already, however, neither describes my situation. On the positive side, I think mine is less complex than the others.

I have only one table (see simplified example below) with info about every car that enters and leaves a car park. I now need to calculate the occupancy by hour to show statistics such as:

• Utilization per day / week / month
• Comparisons to last years utilizations
• days / hours / months of above average utilizations
• ....standard stuff.

The car park has 906 spots.

Thanks a million for your help!

Cheers, Sven

 License Plate Entry Date/Time Exit Date/Time Dummy_1 01/11/2018 00:12:34 05/11/2018 01:14:26 Dummy_2 01/11/2018 02:31:06 03/11/2018 08:56:43 Dummy_3 01/11/2018 03:13:20 02/11/2018 01:01:52 Dummy_4 01/11/2018 03:20:18 04/11/2018 00:13:32 Dummy_5 01/11/2018 03:25:30 03/11/2018 00:08:03 Dummy_6 01/11/2018 03:28:39 04/11/2018 17:47:16 Dummy_7 01/11/2018 03:39:13 01/11/2018 07:59:21 Dummy_8 01/11/2018 03:43:16 04/11/2018 19:11:27 Dummy_9 01/11/2018 03:46:42 05/11/2018 14:27:37 Dummy_10 01/11/2018 03:50:27 05/11/2018 00:45:55 Dummy_11 01/11/2018 03:53:16 04/11/2018 22:57:37 Dummy_12 01/11/2018 03:55:50 04/11/2018 08:48:48 Dummy_13 01/11/2018 03:59:35 04/11/2018 21:04:18 Dummy_14 01/11/2018 04:02:26 04/11/2018 08:51:27 Dummy_15 01/11/2018 04:04:40 03/11/2018 20:27:03

Community Support

Hi @SvenP,

In fact, I think your scenario is more complex because your records contain both date and time parts and you need to analyze across date and time fields.

I try to create two calculated tables to split your records to complete part and remain part.

``````Complete =
SELECTCOLUMNS (
Original,
"cStartDate", IF (
TIMEVALUE ( [Entry Date/Time] ) > TIME ( 0, 0, 0 ),
DATEVALUE ( [Entry Date/Time] ) + 1,
DATEVALUE ( [Entry Date/Time] )
),
"cEnddate", IF (
TIMEVALUE ( [Exit Date/Time] ) > TIME ( 0, 0, 0 ),
DATEVALUE ( [Exit Date/Time] ) - 1,
DATEVALUE ( [Exit Date/Time] )
)
)

Remain =
UNION (
SELECTCOLUMNS (
Original,
"rStartDate", [Entry Date/Time],
"rEnddate", IF (
TIMEVALUE ( [Entry Date/Time] ) > TIME ( 0, 0, 0 ),
DATEVALUE ( [Entry Date/Time] ) + 1,
DATEVALUE ( [Entry Date/Time] )
)
),
SELECTCOLUMNS (
Original,
"rStartDate", IF (
TIMEVALUE ( [Exit Date/Time] ) > TIME ( 0, 0, 0 ),
DATEVALUE ( [Exit Date/Time] ) - 1,
DATEVALUE ( [Exit Date/Time] )
),
"rEnddate", [Exit Date/Time]
)
),
"Diff", DATEDIFF ( [rStartDate], [rEnddate], SECOND )
)
``````

In addition, you also need a calendar table to use as axis of visual and formula calculation.

``````Calendar =
CALENDAR (
MIN ( Original[Entry Date/Time] ) - 1,
MAX ( Original[Exit Date/Time] ) + 1
)
``````

After the above steps, you can write measure formulas to compare and calculate between two table and calendar table on specific date periods. (e.g. if date ranges are included in 'complete' table, it means 100%; if they are included in 'remain' table, you need to manually check and calculate the percent of the range of specific time period)

BTW, for large period(year, month, week, day) calculation, you also need to check and calculate(if they remaining records of small periods) the child period of them to keep the accuracy.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.