Need help number of parked cars by hour

[TrekJournal]

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:

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:

Can anybody tell me what I need to do to get the result I need?

[whitch]

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.

[TrekJournal]

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.

[whitch · 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]) )

[whitch]

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]

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.

[TrekJournal]

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"?