cancel
Showing results for
Did you mean:
Highlighted
Helper II

## [solved] How to calculate time between two changing status

Hello everyone,

my situation is the following. I have a parking that only sends changing states.

It sends a "1" when a car arrived at the parking, then it doesn't send anything until the car leaves again. At that moment the parking sends a "0". I did already the calculation to see the current status but now I need to do analysis over a long time.

So I should be able to see how many hours per day the parking was taken and how many hours it was free.

The data looks like this:

 Parking-ID DateTime Status Comment 10 20.12.2019 16:35 0 Car left 10 20.12.2019 08:22 1 Car arrived 10 19.12.2019 22:47 0 Car left 10 19.12.2019 19:31 1 Car arrived 10 19.12.2019 17:40 0 Car left 10 19.12.2019 07:49 1 Car arrived

How can I calculate per day how many hours the parking was taken and how many hours it was free?

Special request: The hours between 23:00 and 05:00 shouldn't count in this anylsis, how am I doing that?

Thank you and best regards

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User II

## Re: How to calculate time between two changing status

Some changes are required indeed when using multiple Parking-ID's. The first step is the same; make sure you sort by DateTime and add an Index column. Then add a calculated column that adds a ranking per ParkingID based on Index (which reflects the order of Date):

``````RankedIndex =
VAR _curParkID = 'Table'[Parking-ID]
RETURN
RANKX(FILTER('Table', 'Table'[Parking-ID] = _curParkID), 'Table'[Index], , ASC)``````

THen change the calculated Table to this:

``````ParkingActivityMinutes =
GENERATE('Table',
VAR _curParkID = 'Table'[Parking-ID]
VAR _startperiod = 'Table'[DateTime]
VAR _curRankedIndex = 'Table'[RankedIndex]
VAR _nextRankedIndex = IF(COUNTROWS(FILTER(ALL('Table'), 'Table'[RankedIndex] > _curRankedIndex)) > 0, _curRankedIndex + 1, _curRankedIndex)
VAR _endperiod = CALCULATE(MAX('Table'[DateTime]), FILTER(ALL('Table'), 'Table'[RankedIndex] = _nextRankedIndex && 'Table'[Parking-ID] = _curParkID))
RETURN
GENERATESERIES(_startperiod, _endperiod, TIME(0,1,0)))``````

Now this adds up to 3800 rows or something by using twice your example table. You might want to consider using the calculated table as an expression in a aggregated table or something. The logic remains te same 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

Proud to be a Super User!

6 REPLIES 6
Highlighted
Super User II

## Re: How to calculate time between two changing status

Coincidentally, just yesterday I complimented @TomMartens on providing a elegent solution on a similar problem, so credits are for him 😉

First, sort on column DateTime in the PowerQuery Editor (from old to newest) and then add an Index column. Your table now looks like this:

Now we are going to create a generated Table, that creates rows for every minute the care is in a certain state. The DAX for this is the following:

``````ParkingActivityMinutes =
GENERATE(ParkingActivity,
VAR _startperiod = ParkingActivity[DateTime]
VAR _curIndex = ParkingActivity[Index]
VAR _nextIndex = IF(COUNTROWS(FILTER(ALL(ParkingActivity), ParkingActivity[Index] > _curIndex)) > 0, _curIndex + 1, _curIndex)
VAR _endperiod = CALCULATE(MAX(ParkingActivity[DateTime]), FILTER(ALL(ParkingActivity), ParkingActivity[Index] = _nextIndex))
RETURN
GENERATESERIES(_startperiod, _endperiod, TIME(0,1,0)))``````

We are combining two tables here, the original table and a newly generated table (with GENERATESERIES). Now you have the following table:

First few rows of the newly created table.Rows where a status changed from 1 to 0

Now you can create a measure for calculating the minutes by summing column [Status] in a certain date/time range. The Measure is:

``ParkingTaken = SUM(ParkingActivityMinutes[Status])``

Let me know if this works for you! 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

Proud to be a Super User!

Helper II

## Re: How to calculate time between two changing status

In general for this example it works pretty well. The problem is when I come back to my real world case, that has more parking IDs. As there is more then one parking ID in the data the index is mixing between the Ids and it doesn't work anymore.

Do you have any solution when I have rows from multiple parking IDs?

Thank you and best regards

Highlighted
Super User II

## Re: How to calculate time between two changing status

Hello @selimovd

i think its almost impossible to do such a calculation in DAX. To analysis this data I think it's needed to do some transforming before. Try out this code in Power Query and tell me if this could suit you (formatting it's a desaster.. I quickly registered with the GUI)

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc9BCoMwFIThq0jWIm/GVJu37THEhdAIgqsgnr8pFC2RSLbDx8A/DAZiakNpwIYCV6HT9hGn7/yaQrX6eTNjfYXyVDJO+MEphGX379PCHZZU22dP/yCctig8Ra9WSk4lQpc5ZWl+Cu/yWZqfwrv8i83lpzCbP34A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Parking-ID" = _t, DateTime = _t, Status = _t, Comment = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Parking-ID", Int64.Type}, {"DateTime", type datetime}, {"Status", Int64.Type}, {"Comment", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Parking-ID", Order.Ascending}, {"DateTime", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"Parking-ID"}, {{"AllRows", each _, type table [#"Parking-ID"=number, DateTime=datetime, Status=number, Comment=text]}}),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"TableJoin"}),
#"Expanded TableJoin" = Table.ExpandTableColumn(#"Removed Other Columns", "TableJoin", {"Parking-ID", "Status", "Comment", "Column1"}, {"Parking-ID", "Status", "Comment", "Column1"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded TableJoin",{{"Column1", type datetime}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Column1", "DateTime"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"Minute", Int64.Type}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Added Custom3",{{"Time", type time}}),
#"Added Custom4" = Table.AddColumn(#"Changed Type3", "Timeperiod to evaluated", each if Time.Hour([Time])>=23 or Time.Hour([Time])<=5 then false else true)
in

The result looks like this

the calculated indicator "Timerperiod to evaluated" checks if the time it's not after 23 or before 5.

Copy paste this code to the advanced editor to see how the solution works

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

Highlighted
Super User II

## Re: How to calculate time between two changing status

Some changes are required indeed when using multiple Parking-ID's. The first step is the same; make sure you sort by DateTime and add an Index column. Then add a calculated column that adds a ranking per ParkingID based on Index (which reflects the order of Date):

``````RankedIndex =
VAR _curParkID = 'Table'[Parking-ID]
RETURN
RANKX(FILTER('Table', 'Table'[Parking-ID] = _curParkID), 'Table'[Index], , ASC)``````

THen change the calculated Table to this:

``````ParkingActivityMinutes =
GENERATE('Table',
VAR _curParkID = 'Table'[Parking-ID]
VAR _startperiod = 'Table'[DateTime]
VAR _curRankedIndex = 'Table'[RankedIndex]
VAR _nextRankedIndex = IF(COUNTROWS(FILTER(ALL('Table'), 'Table'[RankedIndex] > _curRankedIndex)) > 0, _curRankedIndex + 1, _curRankedIndex)
VAR _endperiod = CALCULATE(MAX('Table'[DateTime]), FILTER(ALL('Table'), 'Table'[RankedIndex] = _nextRankedIndex && 'Table'[Parking-ID] = _curParkID))
RETURN
GENERATESERIES(_startperiod, _endperiod, TIME(0,1,0)))``````

Now this adds up to 3800 rows or something by using twice your example table. You might want to consider using the calculated table as an expression in a aggregated table or something. The logic remains te same 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

Proud to be a Super User!

Highlighted
Super User II

## Re: How to calculate time between two changing status

Hello @selimovd

were you able to solve the problem with any reply given?

If so, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

All the best

Jimmy

Highlighted
Helper II

## Re: How to calculate time between two changing status

Thank you @Jimmy801 and @JarroVGIT  for your help!

The approach of @JarroVGIT worked better for me!

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors