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
Drewz
Helper II
Helper II

How to Calculate and Plot Car Parking Length of Stay

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

 

Sample Parking Data.jpgExample Output.jpg

7 REPLIES 7
v-lionel-msft
Community Support
Community Support

Hi @Drewz ,

 

Please refer to my .pbix file.

lll3.PNG

 

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.

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. 

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
Super User
Super User

@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

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. 

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

@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.

 

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.