Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
TrekJournal
Frequent Visitor

Need help number of parked cars by hour

Hi All,
I have been working on this for days now, and I just can't find the solution, so I hope someone has the anwer for me.

I have data from four different parkinggarages. We want to kwow how many people are parked by the hour (so we know which time of the day is de garage most crowded). And we want to be able to filter by card number.
The other thing is that sometimes they don't use the card to get in or out of the garage, so I want to remove the count if a card number is parked for more than 2 days.


This is an example of my data:

Data_parkeren.png

 

I have this measure to show the people parked in one garage, but it seems that when I use this in a visual it only shows the data from the hours 7, 8 and 9.

 

Cumulatieve Parkeerders =
VAR MaxTijd = MAX('Parkeerbezetting'[DateTime])
VAR MinTijd = MaxTijd - TIME(2,0,0)
VAR TijdRij = 'Parkeerbezetting'[DateTime]
VAR TotaalParkeerders =
SUMX(
    FILTER(
        'Parkeerbezetting',
        'Parkeerbezetting'[DateTime] <= TijdRij && 'Parkeerbezetting'[DateTime] >= MinTijd
    ),
    IF(
        'Parkeerbezetting'[Locatie] = "Nieuwegein IN",
        1,
        IF(
            'Parkeerbezetting'[Locatie] = "Nieuwegein UIT",
            -1,
            0
        )
    )
)
RETURN
TotaalParkeerders
 
The result I would like is something like this:
Bezetting.png
 
Can anybody tell me what I need to do to get the result I need?
 
1 ACCEPTED SOLUTION

Now you've got your [IN DateTime] and [UIT DateTime] columns, what have you changed your [Cumulatieve Parkeerders] measure to?  I think it should be something like this:

 

Cumulatieve Parkeerders =
VAR TijdRij = 'Parkeerbezetting'[DateTime]
RETURN
CALCULATE(COUNTROWS( 'Parkeerbezetting'),'Parkeerbezetting'[IN DateTime] <= TijdRij, 'Parkeerbezetting'[UIT DateTime] >= TijdRij)
+
CALCULATE(COUNTROWS( 'Parkeerbezetting'),'Parkeerbezetting'[IN DateTime] <= TijdRij,'Parkeerbezetting'[IN DateTime] >= TijdRij-2, isBlank('Parkeerbezetting'[UIT DateTime]) )

View solution in original post

6 REPLIES 6
whitch
Resolver I
Resolver I

Also bring through location from the previous row, so you have [Previous Row Location] and [Previous Row DateTime].  Add a new column with this formula:

 

if Text.EndsWith([Previous Row Location], "IN") then [Previous Row DateTime] else null

 

This is the "UIT DateTime" column you need.  You can delete [Previous Row DateTime] once you have this column.

Sorry for the late answer. I tried your suggestion, but I still only get results for the hours 8, 9 and 10. As soon as I put in the date (because I want to see the number of people inside by date and hour it just shows hour 8, 9 and 10.

Now you've got your [IN DateTime] and [UIT DateTime] columns, what have you changed your [Cumulatieve Parkeerders] measure to?  I think it should be something like this:

 

Cumulatieve Parkeerders =
VAR TijdRij = 'Parkeerbezetting'[DateTime]
RETURN
CALCULATE(COUNTROWS( 'Parkeerbezetting'),'Parkeerbezetting'[IN DateTime] <= TijdRij, 'Parkeerbezetting'[UIT DateTime] >= TijdRij)
+
CALCULATE(COUNTROWS( 'Parkeerbezetting'),'Parkeerbezetting'[IN DateTime] <= TijdRij,'Parkeerbezetting'[IN DateTime] >= TijdRij-2, isBlank('Parkeerbezetting'[UIT DateTime]) )

Also, make sure this isn't something in your data: actually look at some examples for the day you're filtering to and make sure there's data there for the whole day

whitch
Resolver I
Resolver I

This is much easier if your data is structured as one row per visit rather than a row for the in and a row for the out. Imagine the first two rows of your example data being condensed into one row with these fields:
Card Number: 20160618
Locatie: Nieuwegein
IN DateTime: 18-7-2023 8:24:00
UIT DateTime: 18-7-2023 15:11:00
IN Uur: 8
UIT Uur: 15

You didn't give a "visit ID" in your sample data, so I assume you don't have one, but even without this you can still shape your data like this in Power Query. You'll need to sort by Card Number & DateTime so the INs and UITs are next to each other, then add an ID column and use this to refer to the next row.

https://goodly.co.in/refer-previous-row-next-row-power-query/

You can then pull any UIT data next to the relevant IN and delete the UIT rows.

Thank you for your reply, I tried like you suggested. There is just one problem with using previous row. There might be an issue with the garage so people don't check out. But they do check in the next time they visit.
So if I would use the previous row it would be inaccurate. 
Do you know how to adapt the formula so that there would be null if the previous row does not contain "IN"?

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.