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
Solved! Go to 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.
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!
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.
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
@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])
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.
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
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.
User | Count |
---|---|
468 | |
54 | |
51 | |
51 | |
49 |
User | Count |
---|---|
453 | |
68 | |
62 | |
61 | |
60 |