Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Estoy trabajando en un proyecto de Power BI, donde estoy tratando de visualizar la ocupación en un estacionamiento frente al tiempo.
Una bahía de estacionamiento puede ser Ocupada o Libre y solo tiene un estado en un momento dado.
Lo que me gustaría es visualizar es cuántas bahías estaban ocupadas en un momento específico (por ejemplo, ayer a las 12 am o 3pm) en un gráfico.
Tengo una tabla de fechas con todas las marcas de tiempo para mi eje X.
¿Cómo puedo calcular qué estado tenía una bahía en un momento dado?
Al visualizar la ocupación, las bahías ocupadas + libres deben en todo momento igualar la cantidad total de bahías en el estacionamiento.
Un ejemplo de mis datos está en el siguiente formato:
bayID | status_startTime | Endtime | Estado |
1 | 2020-10-13 05:37:27Z | 2020-10-13 12:02:42.6593146 +02:00 | Ocupado |
1 | 2020-10-13 10:02:40Z | 2020-10-13 12:02:58.5031439 +02:00 | Gratis |
1 | 2020-10-13 10:02:56Z | 2020-10-13 12:04:03.1440793 +02:00 | Ocupado |
2 | 2020-10-13 10:04:00Z | 2020-10-13 12:04:23.4098165 +02:00 | Gratis |
2 | 2020-10-13 10:04:22Z | 2020-10-13 15:04:40.0415468 +02:00 | Ocupado |
3 | 2020-10-13 13:04:31Z | 2020-10-13 18:40:10.8539946 +02:00 | Ocupado |
1 | 2020-10-13 16:40:08Z | 2020-10-13 18:45:58.7620901 +02:00 | Gratis |
1 | 2020-10-13 16:45:57Z | 2020-10-13 18:49:01.7630668 +02:00 | Ocupado |
Solved! Go to Solution.
Hi @AndHan ,
Thanks for the new data.
I have updated my measures to work on a DateTime table for the axis, but the principle has remained the same.
Measures:
// Bays occupied over time
_bayOccuOverTime =
VAR maxTime = MAX(dateTime[dateTime])
RETURN
CALCULATE(
COUNTROWS(parkingData),
parkingData[status] = "Occupied",
FILTER(
parkingData,
maxTime >= parkingData[startTime]
&& maxTime <= parkingData[endTime]
)
)
// Bays free over time
_bayFreeOverTime =
VAR maxTime = MAX(dateTime[dateTime])
RETURN
CALCULATE(
COUNTROWS(parkingData),
parkingData[status] = "Free",
FILTER(
parkingData,
maxTime >= parkingData[startTime]
&& maxTime <= parkingData[endTime]
)
)
Output in both line chart and stacked column visuals:
It is worth noting that these measures are VERY slow when calculating down to seconds, so you may want to roll up your times to minutes to improve performance.
Also worth noting that your DateTime table should NOT be related to your parkingData table.
Pete
Proud to be a Datanaut!
Hi @AndHan ,
Your updated table didn't work so I entered the data manually.
For my example, I've created two new columns in the data consisting of just the time portion of startTime and endTime as I only have a time table, not a dateTime table.
This is the measure I've used:
_occuOverTime =
VAR maxTime = MAX('time'[time])
RETURN
CALCULATE(
COUNTROWS(xTable),
xTable[status] = "Occupied",
FILTER(
xTable,
maxTime >= xTable[startTimeOnly]
&& maxTime <= xTable[endTimeOnly]
)
)
I get the following output:
Obviously I've only used times down to seconds as that matched with my time table, but the measure principle remains the same for whatever time periods you choose to use.
Pete
Proud to be a Datanaut!
Hi @BA_Pete - Thanks for your suggestion - however it seems like it doesn't provide me with exactly what i want.
I have tried your solution with the full dataset, and it seems like the occupancy and availability is too high.
There is only 66 parking bays at this parkinglot, so the sum of occupied bays and free bays should equal to 66.
Hi @AndHan ,
Difficult to diagnose without seeing the full dataset. However, I did notice that in your example data bay 1 was apparently occupied by two cars 10:03-12:02. Similarly, Bay 2 showed as both Free and Occupied 10:04-12:04.
If your example data came from your full dataset then I'd recommend checking whether there are other multiple occupancies on a single bay, or bays that should be free be are also marked as occupied.
If you are able to share a larger section of the original dataset via OneDrive or similar then I'm happy to take another look. Just make sure that any sensitive data is removed please.
Pete
Proud to be a Datanaut!
Hi again @BA_Pete .
I can see that there's some errors in the example I gave in my post.
A bay should only be able to have a status of Occupied or Free at any given time.
I've added a subset of the full dataset to Dropbox, without these errors.
You should be able to download it from the following link:
https://www.dropbox.com/s/l0pej5hgq9n851a/parkingData.csv?dl=0
I hope you have an idea on how to solve this 🙂
Hi @AndHan ,
Thanks for the new data.
I have updated my measures to work on a DateTime table for the axis, but the principle has remained the same.
Measures:
// Bays occupied over time
_bayOccuOverTime =
VAR maxTime = MAX(dateTime[dateTime])
RETURN
CALCULATE(
COUNTROWS(parkingData),
parkingData[status] = "Occupied",
FILTER(
parkingData,
maxTime >= parkingData[startTime]
&& maxTime <= parkingData[endTime]
)
)
// Bays free over time
_bayFreeOverTime =
VAR maxTime = MAX(dateTime[dateTime])
RETURN
CALCULATE(
COUNTROWS(parkingData),
parkingData[status] = "Free",
FILTER(
parkingData,
maxTime >= parkingData[startTime]
&& maxTime <= parkingData[endTime]
)
)
Output in both line chart and stacked column visuals:
It is worth noting that these measures are VERY slow when calculating down to seconds, so you may want to roll up your times to minutes to improve performance.
Also worth noting that your DateTime table should NOT be related to your parkingData table.
Pete
Proud to be a Datanaut!
Hi @BA_Pete
Thanks for the updated measure. It is now working perfectly! I'm only showing what the status was in half hour blocks, so it's now to slow for my needs 🙂
Hola @AndHan ,
¿Puede compartir su ejemplo de datos en formato de tabla por favor? Puede encontrarlo aquí en la ventana de respuesta:
Esto hará que sea mucho más fácil/rápido para las personas tomar sus datos y ayudarle.
Gracias
Proud to be a Datanaut!
Claro - He actualizado el post 🙂