cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
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-IDDateTimeStatusComment
1020.12.2019 16:350Car left
1020.12.2019 08:221Car arrived
1019.12.2019 22:470Car left
1019.12.2019 19:311Car arrived
1019.12.2019 17:400Car left
1019.12.2019 07:491Car 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
Super User II

Re: How to calculate time between two changing status

Hi @selimovd 

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! 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

6 REPLIES 6
Highlighted
Super User II
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:

image.png

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.First few rows of the newly created table.Rows where a status changed from 1 to 0Rows 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! 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Helper II
Helper II

Re: How to calculate time between two changing status

Thank you already for your approach @djerro123 !

 

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
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]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "ListDateTimes", each Table.FromColumns({ List.DateTimes(List.Min([AllRows][DateTime]), Duration.TotalMinutes(List.Max([AllRows][DateTime])-List.Min([AllRows][DateTime]))+1,#duration(0,0,1,0))})),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "TableJoin", each Table.FillDown( Table.Join([AllRows],"DateTime", [ListDateTimes],"Column1",JoinKind.FullOuter),{"Parking-ID", "DateTime","Status","Comment"})),
    #"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"}}),
    #"Added Custom2" = Table.AddColumn(#"Renamed Columns", "Minute", each 1),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"Minute", Int64.Type}}),
    #"Added Custom3" = Table.AddColumn(#"Changed Type1", "Time", each Time.From([DateTime])),
    #"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
    #"Added Custom4"

 

The result looks like this

image.png

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
Super User II

Re: How to calculate time between two changing status

Hi @selimovd 

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! 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Super User II
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
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!

 

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

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