Need Help - calculate occupancy of parking lot

[Sven01]

Dear all, my dataset includes the datetime of arrival and departure of a car parking spot and also the information which parking position. What i need to know is the utilization (how many hours) of each parking lot in the timewindow between 21pm and 3am. The dataset contains data from one full year. Did anyone has an idea of how to solve these problem?

Thank you for your time and response 

[Seward12533]

Please let us know what your data looks like. Makes a difference in the solution. Is there only one date column and a attribute to define if it’s arrival or departure or two date/time columns one for arrival and one for depature. Is it date time in a single value or separate date and time columns etc.

Assuming it’s a single table with a single date/time column and attribute the approach is something like this

Use a calculated column for every departure calculate the associated arrival time using CALCULATE and EARLIER
Measure to sum the elapsed time filtering out the rows that have arrival times only.

[Sven01]

Hi @Seward12533,

Thanks for your reply. attched you will find the sample dataset:

sample dataset - parking time between 9pm and 3am

As you can see there are two date column, one for arrival and one for departure. the third one is the information about the parking position. i added a second sheet with the relevant timewindow in each day for 2017 (9pm to 3am).

What i need to know is the occupancy time of each parking position in relevant timewindow sum up over full year 2017.

Thanks a lot for your Time!

[Seward12533 · Accepted solution]

Here is an Example. To make it easier and since the relevant hours were all the same I used a disconnected slicer to collect the start hour and duration of the window and used that instead.  You could do it with a lookup table if you really need to. For the disconnected tables (just hours of day and numbers from 1-24) you need harvester measures to collect the values.  I used SELECTEDVALUE to do this and set defaults for when more than one or none are selected.

The Main calculation is this one

Utilized Hours Relevant = SUMX('Year 2017',
    VAR RStart = [Date]+[Selected Hour]
    VAR REnd = RStart+TIME([Selected Duration],0,0)
    VAR SDate = IF([Arrival]<RStart,RStart,[Arrival])
    VAR EDate = IF([Departure]>REnd,REnd,[Departure])
    VAR Result = CALCULATE(DIVIDE(DATEDIFF(SDate,EDate,MINUTE),60),DimDate)
    RETURN
    IF(Result>0,Result) // If result is Negative parked outside of relavent window so ignore
    )

I then add this as a calcualted column to the table. (I used the IF([Arrival] to block this calculating for the hundreds of thousands of blank rows in your data set. (TIP when importing from Excel always create a table first and then import or link to that table rather than the sheet. Did something similar for the number Relevant Hours and then defined a measures to sum up these calc columns and then divide those to get pct.  I also defined Parkign Spot Type Lookup (in this case just the row numbers.

Also Note I orignally used SUMX to calcualte the Relevant Hours etc as measures but this was very SLOW because of the interations involved when bulding the table. When I convered to using calculated columns I never rewrote these but should replace the SUMX with Just Sums or as a pure calcuated column since in a row the filter context is just that one row anyway. I just never took the time to fix.

I did notice some results that didn't make sence but I think that is because there is some duplicate data in your test data.

Here is one example

The Matrix allows you do drill down within the month to look at the details and validate the calulations.

[Sven01]

Hi @Seward12533,

great job and really smart solution! Thank you so much for your time.

So far i understand everything. I validate the calculation and noted one little deviation.

As you see i choose the relevant time window from 10pm to 6am. The calculation in the first row is correctly (453min are relevant), but in the second its not (0). I guess the reason for that is due to the incorrect relevant time window in that case 2017-01-02 22pm to 2017-01-02 6am - but should be 2017-01-01 22pm to 2017-01-02 6am, right? Otherwise the result is negatve (Departure - RStart)

Utilized Hours Relevant = SUMX('Year 2017',
    VAR RStart = [Date]+[Selected Hour]
    VAR REnd = RStart+TIME([Selected Duration],0,0)
    VAR SDate = IF([Arrival]<RStart,RStart,[Arrival])
    VAR EDate = IF([Departure]>REnd,REnd,[Departure])
    VAR Result = CALCULATE(DIVIDE(DATEDIFF(SDate,EDate,MINUTE),60),DimDate)
    RETURN
    IF(Result>0,Result) // If result is Negative parked outside of relavent window so ignore
    )

Would be awesome if you have another TIP for me.

Thanks a lot!

Sven

[Seward12533]

@Sven01 your picture didin't load so Im having trouble following. Can you provide details on the specific case.  But your right departure should never be less than RStart.

You may need to play with the logic for the RStart to handle the case if the Arrival is after Midnight and within the relavent window of the previous day.   Woudl be IF stattmet to subtract a day in that case. Something like this but never tested it.

RSTart = [Date]-If(TIME([Arrival])<[Selected Hour],1,0)+[Selected Hour])

[Sven01]

@Seward12533 sorry, hope you can see it now.

[Seward12533]

Didnt' both to test this, but looking at the dax its not quite right but the is sound.  Howeer, I noticed another problem with extended stays. This method will only count the first day and they park for 30days then it will miss the otehr 29. So alas it might not work for you. 

If I find some time I can try but I already have a bunch of hours into this and its not trivial.  What I think you need to do is something like this. 

• Unpivot your data so you have separate entries for Arrival and Departure with dates in a single column and Parameter to indicate if its arrival or departure. 
• Build a Date-Time-Minute table and then use diconnected slicers to harvest Start Window and End Window
• Write measures to count the number of minutes by counting rows in the Date-Time-Minute table between the arrival and departure time both for all minutes (Available) after filttering out all the Date-Time-Minutes outside of the window (utilized)

Even though the Date-Time-Minute table will be HUGE it will probably be faster and more responsive than this approach since it does not need SUMX and the way PowerBI works this is WAY more efficient. 

I'm not sure I'll have the time to get to it soon so you might consider posting this again with a fresh post referncing this one.  Also try the PBI User Group 

[Sven01]

@Seward12533

just want to say thank you for your help. Not quite sure if iam able to translate your second advise into dax funktion but i will try my best. Let me know if you find some time and muse to once again take care of the matter.