cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Calcular la ocupación en una fecha y hora específica

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:

bayIDstatus_startTimeEndtimeEstado
12020-10-13 05:37:27Z2020-10-13 12:02:42.6593146 +02:00Ocupado
12020-10-13 10:02:40Z2020-10-13 12:02:58.5031439 +02:00Gratis
12020-10-13 10:02:56Z2020-10-13 12:04:03.1440793 +02:00Ocupado
22020-10-13 10:04:00Z2020-10-13 12:04:23.4098165 +02:00Gratis
22020-10-13 10:04:22Z2020-10-13 15:04:40.0415468 +02:00Ocupado
32020-10-13 13:04:31Z2020-10-13 18:40:10.8539946 +02:00Ocupado
12020-10-13 16:40:08Z2020-10-13 18:45:58.7620901 +02:00Gratis
12020-10-13 16:45:57Z2020-10-13 18:49:01.7630668 +02:00Ocupado
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

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:

BA_Pete_0-1603101222994.png

 

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

View solution in original post

8 REPLIES 8
Highlighted
Super User I
Super User I

Hola @AndHan ,

¿Puede compartir su ejemplo de datos en formato de tabla por favor? Puede encontrarlo aquí en la ventana de respuesta:

arrakha.PNG

Esto hará que sea mucho más fácil/rápido para las personas tomar sus datos y ayudarle.

Gracias

Highlighted

Claro - He actualizado el post 🙂

Highlighted
Super User I
Super User I

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:

andHan.PNG

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

Highlighted

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.

Udklip.PNG

Highlighted

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

Highlighted

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 🙂

Highlighted

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:

BA_Pete_0-1603101222994.png

 

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

View solution in original post

Highlighted

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 🙂  Udklip.PNG

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors