Measure subtract if cardnumber does not check out

[TrekJournal]

Hi,

I need some help with a measure:

VAR __Datum = MAX('Garage UT'[DateTimeG])
VAR __InTotaal =
    CALCULATE(
        COUNT('Garage UT'[Card Number]),
        'Garage UT'[In/UitG] = 1,
        'Garage UT'[DateTimeG] <= __Datum,
        ALLSELECTED('Garage UT')
    )
VAR __UitTotaal =
    CALCULATE(
        COUNT('Garage UT'[Card Number]),
        'Garage UT'[In/UitG] = 2,
        'Garage UT'[DateTimeG] <= __Datum,
        ALLSELECTED('Garage UT')
    )
RETURN
    IF(COUNT('Garage UT'[Card Number]) <> BLANK(),
    __InTotaal - __UitTotaal
    )

It is working perfectly, but it can happen that a cardnumber is inside, but forgets to check out.

I want to make it that when a cardnumber is inside (__InTotaal) and does not check out (__UitTotaal) within 2 days, it should subtract the total count.

Does anyone have a great idea how to get this to work?

Regards,
Bianca

[lbendlin]

what should happen when you have two checkins for the same card number but only one checkout?  are card numbers static or are they rotating?

Why 2 days?  is there a reason behind that limit?  Or do you rather want to have a report that pairs all Ins with all Outs and shows the time between? So you can see the patterns better.

[TrekJournal]

Thank you for your reply.

What I'm trying to show is how many card numbers are inside by hour and day.
We want to know how much parkingspaces are occupied at hourly basis on any given day.
But card can come inside one day and leave another day.
We concluded that no card stays more than 48 hours, thats why I need to subtract the count when a card is inside more than 2 days (or 48 hours).

My data looks like this:

Card Number	In/UitG	DateTimeG
12345	1	4-5-2024 11:21:22
45678	1	4-5-2024 15:34:19
12345	2	5-5-2024 18:10:32
67891	1	3-5-2024 07:03:00
45678	1	7-5-2024 15:47:11
67891	1	6-5-2024 06:54:29

So card number 12345 checked in on the fourth and left on the fifth. the other two didn't check out, but checked in on another day.

[lbendlin]

Oooh, that's a lot of assumptions.  What if they go on vacation?  Or even a long weekend?  What if they mess with you and use the same card for different cars?

[TrekJournal]

Well that could be the case, but let's assume that an employee will not stay parked in the garage while they are on vacation.
They cannot go back inside, unless the card gets "reset". Unfortunatly the system does not show the card numbers that get reset (else my life would be that much easier ;-)).

Do you have any suggestions in how to make this work?

[lbendlin · Accepted solution]

I would separate the Ins and Outs, and then for each In find a corresponding out within 48 hours (or flag it if that is absent)

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "ZY5LCsQwDEOvMnjdguVP0uoqIfe/Rk1nWgYC0u7xpDEE5pGyCaqx525q8QFoFZO5DYls/ViIpAdx3sTjsGq+xEEo/esow4mfwx9CO9Wpuqz0v5XoBBZHex2NGbT6MS8=",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [#"Card Number" = _t, #"In/UitG" = _t, DateTimeG = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"DateTimeG", type datetime}}, "nl"),
  #"Added Custom" = Table.AddColumn(
    #"Changed Type",
    "Matched UitG",
    (k) =>
      try
        Table.SelectRows(
          #"Changed Type",
          each [Card Number]
            = k[Card Number] and [#"In/UitG"]
            = "2" and [DateTimeG]
            > k[DateTimeG] and [DateTimeG]
            < k[DateTimeG]
            + #duration(2, 0, 0, 0)
        ){0}[DateTimeG]
      otherwise
        null,
    type datetime
  )
in
  #"Added Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.