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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AndHan
Frequent 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

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

8 REPLIES 8
BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

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.

Udklip.PNG

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

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:

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

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

BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Claro - He actualizado el post 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors