Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
DreDre
Helper II
Helper II

Given 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. 

DepLocEqpRegionArrInLocDepLoc DateTime
111BEast1/25/2021 19:201/25/2021 23:45
111BEast1/24/2021 22:571/25/2021 10:00
111BEast1/23/2021 22:541/26/2021 4:15
14QEast1/25/2021 6:541/25/2021 12:54
113AEast1/25/2021 20:591/26/2021 8:12
14QEast1/25/2021 21:011/25/2021 23:21
113AEast1/25/2021 21:551/26/2021 11:35
113AEast1/25/2021 16:281/25/2021 21:58
111BWest1/24/2021 19:481/25/2021 12:57
111BWest1/25/2021 7:271/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 

1 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] )
)
)
 

arrival.JPG

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
vanessafvg
Super User
Super User

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?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




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

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])))

yingyinr_0-1613473499041.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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. image.png

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

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] )
)
)
 

arrival.JPG

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@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!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors