Counting Cars in a Parking Lot at any point in time

[rsbin]

Hello Folks,

Am in need of some help on this one.  Although what I am describing is a fictitious scenario, it is analagous to my real-world problem that I need to solve.

I have a parking lot in which cars enter and exit all day long.  I need a calculated column, "CarsinLot" that will determine the Count of Cars at any point in time. In the following mock up, CarID represents the same car. 

Index	CarID	Event	DateTime	CarsinLot
1	30186	Arrival	1/1/2021 0:13	1
2	14429	Arrival	1/1/2021 0:20	2
3	114822	Arrival	1/1/2021 0:21	3
4	40526	Arrival	1/1/2021 0:38	4
5	40526	Exit	1/1/2021 0:38	3
6	30186	Exit	1/1/2021 0:44	2
7	24946	Arrival	1/1/2021 0:48	3
8	14430	Arrival	1/1/2021 1:45	4
9	114822	Exit	1/1/2021 3:36	3
10	114830	Arrival	1/1/2021 3:37	4

For every row, I need to determine if it is an Arrival (+1 to Earlier ) or an Exit (-1 from Earlier).

I have never used the EARLIER function, but current reading suggest using Variables instead.  Also not sure if this is best done in Power Query or DAX.

Any assistance would be much appreciated.

Kind Regard to All,

[v-xiaoyan-msft]

Hi @rsbin ,

Does the replies above solve your problem? If it has been solved, please mark the correct reply as the standard answer to help the other members find it more quickly.Thanks in advance for your kind cooperation!

Hope it helps,

Community Support Team _ Caitlyn

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

[sevenhills]

I think what you are doing is similar to Sales Transactions that has both sales and returns, and you want to know the Account balance at any given time. 

If it is a measure then you can do like this...

Cars In Lot = 
 CALCULATE( count ('Table'[Index]), FILTER( ALL('Table'), 'Table'[DateTime] <= Max('Table'[DateTime]) && 'Table'[Event] = "Arrival" ))
 - 
 CALCULATE( count ('Table'[Index]), FILTER( ALL('Table'), 'Table'[DateTime] <= Max('Table'[DateTime]) && 'Table'[Event] = "Exit" ))

[sevenhills]

My 2 cents:

I prefer to do as custom column in power query than calculated column, and I will leave the decision to you. My rationale is DAX consume too much memory and also what you are looking is within the same table.

Calculated column as per your reply:

Column Cars In Lot = 
countx( filter ('Table', 'Table'[Event] = "Arrival" && 'Table'[DateTime] <= EARLIER('Table'[DateTime])), 'Table'[Index])
- 
countx( filter ('Table', 'Table'[Event] = "Exit" && 'Table'[DateTime] <= EARLIER('Table'[DateTime])), 'Table'[Index])

[rsbin]

@sevenhills,

Well it looks like you were correct.  The DAX didn't work - left it spinning for 10 minutes, then decided to shut 'er down.  

I have no issues doing this in Power Query if you can point me in the right direction.  I have done a little bit of reading where you create 2 index columns and do some nesting or something like that.  Is this what you had in mind?

[sevenhills · Accepted solution]

Power Query:

Columns:

"RT Cars in Lot" and "RT Cars in Lot 2" are same, you can pick which ever way you want.

Note: You may need to incorporate List.Buffer if you have more than 100K rows in the "RT Cars in Lot". 

Optional: 

"RT Cars in Lot - Arrival Cars", "RT Cars in Lot - Exit Cars" are optional, added in my way of doing things, which helps to address questions like how many arrived and exited at a given point of time. 

M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc87CsMwEATQq4StDd6fZEldipxCqEhpSGVCyPGzXqcQiVVJDI8dplYgmECQUrT3um3r6/6wH800MzJdsJBAmyrwnqpyHjlGd7KnpIl5CMmhWqoYeFgsyV3o3O29Pk9R7Fb8I1VHi6WsWYeNehxLx1TBc0dFg7vcT/1tlSLRFeGXje6ZXKC1Dw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, CarID = _t, Event = _t, DateTime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"CarID", Int64.Type}, {"Event", type text}, {"DateTime", type datetime}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom Calc", each if [Event] = "Arrival" then 1 else if [Event] = "Exit" then -1 else 0),
    #"Added Custom" = Table.AddColumn(#"Added Conditional Column", "RT Cars in Lot", each List.Sum (List.FirstN(#"Added Conditional Column"[Custom Calc], [Index]))),
    #"Added Custom 2" = Table.AddColumn(#"Added Custom","RT Cars in Lot 2",(i)=>List.Sum(Table.SelectRows(#"Added Custom", each [Index]<=i[Index]) [Custom Calc]), type number),
    #"Added Custom 3" = Table.AddColumn(#"Added Custom 2","RT Cars in Lot - Arrival Cars",(i)=>List.Sum(Table.SelectRows(#"Added Custom 2", each i[Event] = "Arrival" and [Index]<=i[Index]) [Custom Calc]), type number),
    #"Added Custom 4" = Table.AddColumn(#"Added Custom 3","RT Cars in Lot - Exit Cars",(i)=>List.Sum(Table.SelectRows(#"Added Custom 3", each i[Event] = "Exit" and [Index]<=i[Index]) [Custom Calc]), type number),
    #"Filled Down" = Table.FillDown(#"Added Custom 4",{"RT Cars in Lot - Arrival Cars", "RT Cars in Lot - Exit Cars"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Custom Calc"})
in
    #"Removed Columns"

Explanation (as you mentioned below post):

RT - Running Total

• Source ... In Power Query, when you use Enter data, it creates this step. just the data for testing. 
• #"Changed Type" ... just the way power query behaves after you have some sample data entered
• #"Added Conditional Column" ... adding "1" for arrival, "-1" for Exit, "0" for other => Custom Calc. This is important as we use this for rest of calculations.
  • Say if you have Amount for sales transactions, you use Amount in place of 1. Just to indicate whether it is positive in case of sales, and negative value in case of returns for RT.
• #"Added Custom" ... Select all the rows with index current and previous row. You can use datetime also here. Sum the Custom Calc, which gives the RT.
• #"Added Custom 2" ... other way to do the same RT
• #"Added Custom 3" ... similar to #"Added Custom 2", only for Arrival
• #"Added Custom 3" ... similar to #"Added Custom 2", only for Exit
• #"Filled Down"  ... Now you fill down the empty rows from the previous row not null values, this way we know at any time what is Arrival count and Exit count
• #"Removed Columns" ... removed the Custom Calc column, as we dont need it for real scenario

Data in Power Query:

Hope it helps ~!

[rsbin]

@sevenhills 

Hope you don't mind, but am having trouble understanding where I need to insert the List.Buffer statement.  I've tried multiple things without any luck.

And Thanks Again!

[sevenhills]

I am not sure you got this point. You only need one of these, as they both gives RT: 

• #"Added Custom"
• #"Added Custom 2"

I provided both ways for your needs.

List.Buffer or Table.Buffer has to be before #"Added Custom". 

List.Buffer or Table.Buffer is used to speed up as we are calculating so many iterations of data. I will say, first get your solution ready and then improve by adding them.

https://docs.microsoft.com/en-us/powerquery-m/list-buffer 

https://docs.microsoft.com/en-us/powerquery-m/table-buffer 

[rsbin]

@sevenhills,

It will take me some time to understand the Code, but looks like it will be a viable solution.  Will mark it as such once I get it working.

I do have in excess of 500K records so will definitey have to look at how to incorporate the List.Buffer you mentioned.

Much Thanks for your guidance through this thread!

Best Regards,

[sevenhills]

I updated with my explantion of the Power Query Reply above, to maintain the context. Please check.

[rsbin]

@sevenhills,

Appreciate the response, but as I explained to @bcdobbs, in order to perform further analysis, I really think I need a Calculated Column.  Is it possible to convert your Measure solution?

Thanks again,

[bcdobbs]

Is there a reason it needs to be a calculated column.

I think I'd split the date/time column into two separate ones one for date and one for time. Then create a date table and a time

table related to them. 

Cars in car park at a specific time can then be done with a measure which counts entries from start of day and subjects count of exits from start of day.

Can mock something as an example of you'd like?

[rsbin]

@bcdobbs,

Appreciate the response.  Reason for a Calculated Column is to be able to do much more in-depth analysis.

For example what hour of day do we reach capacity.  How many times a week or a month we reach capacity, etc. 

I do have a Date table and time table.  However, my real-world operation is 24 / 7/ 365, so doing it this way gives me a continuous count over the entire range of my data set.

I have just viewed a video that has given me a fairly simple idea as to how to work this.  Will let you know if doesn't work and may lean on you for further guidance.

Thanks again for the response.

[zekiAbdul]

I know this thread is 3 yrs old, but I have a similar situation where I need to create a real time count of empty spaces available in the parking lot. You mention about a video that you viewed and might have helped you in resolving your issue. If its still avail., where can I refer the video? Also, which of the above pbi solutions helped you? Thank you