How to Calculate and Plot Car Parking Length of Stay

[Drewz]

Hi All,

I've been searching for info on how Power BI could do what I want, but I'm still stuck.

I have on-road car parking data between 7 am and 7 pm over 3 days. Each parking space is checked every hour and the number plate recorded if a vehicle is parked in the parking space. See example of attached data (without using real number plates). I'm trying to figure out how I can create a graph that shows the frequency of car parking length. E.g. I want to total for each day the number of cars that parked for 1 hour, 2 hours, etc. I've attached an example of the type of bar graph I'd like to produce.

Hope someone can help. 

Cheers Drew

[v-lionel-msft]

Hi @Drewz ,

Please refer to my .pbix file.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Drewz]

Hi @v-lionel-msft ,

thanks for that. Not quite what I was looking for. On the x-axis I wanted to put duration of parking, but your idea gives me a good option for another way to present the data. Thanks. 

[v-lionel-msft]

Hi @Drewz ,

Even if you want the x-axis display the [Hour] column to be roughly the same, if you encounter any problems, please post.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Drewz , I do not see any end time to calculate no of hours.

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

But refer these articles, these can help in creating that hour bucket

https://www.daxpatterns.com/dynamic-segmentation/
https://www.daxpatterns.com/static-segmentation/
https://www.poweredsolutions.co/2020/01/11/dax-vs-power-query-static-segmentation-in-power-bi-dax-po...
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

[Drewz]

Hey Amit, it's not start and end time, it's basically a count of each hour a car with number plate is parked. For example, if a car with plate number XYZ123 is parked in car park number 1 at 7am, 8am and 9 am, then I'd just count that as parked for 3 hours on that day. 

[amitchandak]

@Drewz , then you try for dynamic segmentation / binning . Check the link I shared if they can help

[Drewz]

@amitchandak 

Looking at the links, I think static banding might work and my bins are fixed. E.g. the count of parked cars was 12 hours, so the range of parking length will be between 1 and 12 hours. I created a table called PBAND for the parking bands. 

To avoid showing number plates, I've created a sample data set counting 8 car parks for 3 hours (7am, 8am and 9am) on 2 streets over 3 days. I was going to attach the pbix file, but that doesn't seem to be an option. The maximum car stay length is 3 hours per day. 

I tried the following fromula, but there's an error when I try to make it equal the band.

Parking band total =

CALCULATE(

COUNTA(Parking[Plate #]),

FILTER(Parking, Parking[Plate #]=band))

I'm not even sure, this will give me what I want. I'm worried that this will calculate the total parking over the 3 days and I won't be able to split out per day. In Excel I'd just use a pivot table and then plot the graph. Just a bit lost how to use DAX.