Skip to main content
cancel
Showing results for 
Search instead 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

Reply
SvenP
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 PlateEntry Date/TimeExit Date/Time
Dummy_101/11/2018 00:12:3405/11/2018 01:14:26
Dummy_201/11/2018 02:31:0603/11/2018 08:56:43
Dummy_301/11/2018 03:13:2002/11/2018 01:01:52
Dummy_401/11/2018 03:20:1804/11/2018 00:13:32
Dummy_501/11/2018 03:25:3003/11/2018 00:08:03
Dummy_601/11/2018 03:28:3904/11/2018 17:47:16
Dummy_701/11/2018 03:39:1301/11/2018 07:59:21
Dummy_801/11/2018 03:43:1604/11/2018 19:11:27
Dummy_901/11/2018 03:46:4205/11/2018 14:27:37
Dummy_1001/11/2018 03:50:2705/11/2018 00:45:55
Dummy_1101/11/2018 03:53:1604/11/2018 22:57:37
Dummy_1201/11/2018 03:55:5004/11/2018 08:48:48
Dummy_1301/11/2018 03:59:3504/11/2018 21:04:18
Dummy_1401/11/2018 04:02:2604/11/2018 08:51:27
Dummy_1501/11/2018 04:04:4003/11/2018 20:27:03

 

1 REPLY 1
v-shex-msft
Community Support
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,
    "License Plate", [License Plate],
    "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 = 
ADDCOLUMNS (
    UNION (
        SELECTCOLUMNS (
            Original,
            "License Plate", [License Plate],
            "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,
            "License Plate", [License Plate],
            "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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.