Forum Discussion
Helper IIGiven arrival and depart datetime in the same row, chart data over multiple days
I am stuck on this one and have tried a couple methods from the forum, but maybe someone can help with this
I have a list of data that shows what time a car arrives at a facility and what time it departs. This is not always on the same day, but we only have so many parking spots at each location. The goal is to chart total number of parked cards over multiple datetimes.
If I am looking at the attached data for DepLoc 1 on 1/25/2021 at 7am, I should see 4, but If I look at 8am I would see 5 because of one additional arrival. The other columns (DepLoc, Eqp, Region) are slicers on the page for users to choose the data they want to see.
| DepLoc | Eqp | Region | ArrInLoc | DepLoc DateTime |
| 1 | 11B | East | 1/25/2021 19:20 | 1/25/2021 23:45 |
| 1 | 11B | East | 1/24/2021 22:57 | 1/25/2021 10:00 |
| 1 | 11B | East | 1/23/2021 22:54 | 1/26/2021 4:15 |
| 1 | 4Q | East | 1/25/2021 6:54 | 1/25/2021 12:54 |
| 1 | 13A | East | 1/25/2021 20:59 | 1/26/2021 8:12 |
| 1 | 4Q | East | 1/25/2021 21:01 | 1/25/2021 23:21 |
| 1 | 13A | East | 1/25/2021 21:55 | 1/26/2021 11:35 |
| 1 | 13A | East | 1/25/2021 16:28 | 1/25/2021 21:58 |
| 1 | 11B | West | 1/24/2021 19:48 | 1/25/2021 12:57 |
| 1 | 11B | West | 1/25/2021 7:27 | 1/25/2021 11:37 |
Given this data how could I do a line or bar chart that shows this count based on the date I select? The cars that arrive on 1/24 and depart on 1/26 are still taking a parking space and should be counted for 1/25. Any help or guidence is greatly appreciated!
Hi DreDre ,
I'm so sorry that I made a mistake when update the measure: written the field [DepLoc DateTime] as [DepLoc] wrongly. That's why you can't get any value....
Measure =CALCULATE (COUNT ( 'Sheet1'[Eqp] ),FILTER (ALL('Sheet1'),'Sheet1'[ArrInLoc] <= SELECTEDVALUE ( 'Date'[DateTime] )&& 'Sheet1'[DepLoc DateTime] >= SELECTEDVALUE ( 'Date'[DateTime] )))Best Regards
8 Replies
- vanessafvg
Community Champion
if you can share your pbix from dropbox or something that would be really useful.
what is it that you are expecting to see on a graph, a list of the cars and the date and time of day they are in a parking or the parking is being used?
DreDreI didnt' think about that as an option. Here is the link
SAMPLE PBIXMy goal is just to see a total count at any point during the time period selected as a full day. People generally filter by date to see if each lot has space in order to see if we need to move some cars around to make room. I do something similar in Excel today and I added that to my data sample as well on Sheet2. Ultimatly something along those lines
Hi DreDre ,
I updated your sample pbix file(see attachment), please check whether that is what you want.
1. Create a date table
Date = ADDCOLUMNS ( CROSSJOIN ( CALENDAR ( DATE ( 2021, 1, 1 ), TODAY()), UNION ( ROW ( "Time", TIME ( 1, 0, 0 ) ), ROW ( "Time", TIME ( 2, 0, 0 ) ), ROW ( "Time", TIME ( 3, 0, 0 ) ), ROW ( "Time", TIME ( 4, 0, 0 ) ), ROW ( "Time", TIME ( 5, 0, 0 ) ), ROW ( "Time", TIME ( 6, 0, 0 ) ), ROW ( "Time", TIME ( 7, 0, 0 ) ), ROW ( "Time", TIME ( 9, 0, 0 ) ), ROW ( "Time", TIME ( 10, 0, 0 ) ), ROW ( "Time", TIME ( 11, 0, 0 ) ), ROW ( "Time", TIME ( 12, 0, 0 ) ), ROW ( "Time", TIME ( 13, 0, 0 ) ), ROW ( "Time", TIME ( 14, 0, 0 ) ), ROW ( "Time", TIME ( 15, 0, 0 ) ), ROW ( "Time", TIME ( 16, 0, 0 ) ), ROW ( "Time", TIME ( 17, 0, 0 ) ), ROW ( "Time", TIME ( 18, 0, 0 ) ), ROW ( "Time", TIME ( 19, 0, 0 ) ), ROW ( "Time", TIME ( 20, 0, 0 ) ), ROW ( "Time", TIME ( 21, 0, 0 ) ), ROW ( "Time", TIME ( 22, 0, 0 ) ), ROW ( "Time", TIME ( 23, 0, 0 ) ), ROW ( "Time", TIME ( 24, 0, 0 ) ) ) ), "DateTime", [Date] + [Time] )2. Create a measure to get the count
Measure = CALCULATE(COUNT('Sheet1'[Eqp]),FILTER('Sheet1','Sheet1'[ArrInLoc]<=SELECTEDVALUE('Date'[DateTime])))Best Regards
