Given arrival and depart datetime in the same row, chart data over multiple days

[DreDre]

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!

SAMPLE PBIX 

SAMPLE DATA - With example of end product in excel 

[vanessafvg]

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?

[DreDre]

I didnt' think about that as an option. Here is the link 
SAMPLE PBIX 

SAMPLE DATA 

My 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

[v-yiruan-msft]

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

[DreDre]

Not quite, that does show an increase in the total number but does not go down as cars are removed or include arrivals from prior days. The start/end of 1/25 should be 3 cars, but going up and down throughout the day. 

[v-yiruan-msft]

Hi @DreDre ,

Sorry for delay. Please update the formula of measure as below and check whether it can return the correct result:

Measure =
CALCULATE (
    COUNT ( 'Sheet1'[Eqp] ),
    FILTER (
        'Sheet1',
        'Sheet1'[ArrInLoc] <= SELECTEDVALUE ( 'Date'[DateTime] )
            && 'Sheet1'[DepLoc] >= SELECTEDVALUE ( 'Date'[DateTime] )
    )
)

Best Regards

[DreDre]

Hello @v-yiruan-msft, not sure if you made an additional adjustment to the file aside from the measure, but when I update the measure with what you have above I do not see any data. Is there something I am missing here?

Sorry for the delay in responding, I have been out of town, but I do not think this solves the problem quite yet.

[v-yiruan-msft · Accepted solution]

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

[DreDre]

@v-yiruan-msft THAT DID IT! Thank-you so much for your help on this! I am newer to PBI and this one really stumpted me. I appreciate your help figuring this out!