Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 DATA - With example of end product in excel
Solved! Go to 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
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?
Proud to be a Super User!
I didnt' think about that as an option. Here is the link
SAMPLE PBIX
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])))
Best Regards
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.
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
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] )
)
)
|
Best Regards
@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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |