Calculating time difference between alarms

DAX
1

Alarm Model.pbix (1.5 MB)
Au 5day analysis.xlsx (513.4 KB)
Hn 5 day analysis.xlsx (101.0 KB)
Ns 5 day analysis.xlsx (77.0 KB)
Wn 5 day analysis.xlsx (1.0 MB)

Hi experts, I have been trying to calculate the length of time a device is off in the attached model. I have tried summarise but (for me) the problem is too complex.

A device can go off and on multiple times during a day and I need to capture the time between each off and on instance The first image highlights the problem. Note the first instance is always off followed by on. If a day starts with on there will be corresponding off the day before. Likewise an off at the end of the day will have a corresponding on the following day. (This is only relevant to the condensed data set provided)

image
image466×558 15.2 KB

I am trying to isolate instances where the device is turning itself off and on. In the picture above you can see this clearly. The second image shows a device where a person is switching it off and on.

image

Requirements

  1. calculate total time for each instance the device is off, seconds, minutes, hours, days.
  2. summarise total time device is off
  3. count the number of instances the device is off, filtered by length of time. ie 100 instances between 1-3 minutes.
  4. be able to graph the above by day and location (location data not provided)

Really appreciate any help you can provide.

regards
Steven

2

Hey stevens,

please try with below DAX code
Time Diff =
// Given a number of seconds, returns a format of “hh:mm” VAR vDuration
= SUMX (TimeTracker, DATEDIFF (TimeTracker [Start_Time], TimeTracker [End_Time], SECOND)) // There are 3,600 seconds in an hour VAR vHours = INT (vDuration / 3600) // There are 60 seconds in a minute VAR vMinutes = INT (MOD (vDuration - (vHours * 3600), 3600) / 60) // These intermediate variables ensure that we have leading zeros concatenated onto single digits // Hours with leading zeros VAR vHoursFormatted = IF (LEN (vHours) = 1, “0” & vHours, “” & vHours) // Minutes with leading zeros VAR vMinutesFormatted = IF (LEN (vMinutes) = 1, “0” & vMinutes, “” & vMinutes) // Now return hours and minutes with leading zeros in the proper format “hh:mm” VAR vResult = vHoursFormatted & “:” & vMinutesFormatted RETURN IF (vResult = “:”, BLANK (), vResult

Regards,
Pankaj

3

Thanks Pankaj, really appreciate your feedback.

How do you identify the start time of each sequence of OFF/ON and its corresponding end time on each device?

I need to be able to identify the downtime between the first off marker and the next on marker. The report needs show:

Device 1011:
turned off at 18/02/23 9:47:37 a.m. and was off for 1mm:10ss
turned off at 18/02/23 2:26:38 a.m. and was off for 1mm:20ss
and so forth

image
image393×548 14.7 KB

1 Like
4

Hey,

May be you have to create one additional column using switch statement .
To have on /off i. Single col.
Please try with above trick

5

Alarm Model.pbix (1.7 MB)

Hi Pankaj, I created a last date column and included in the time diff and keep getting 0 as the answer.

What am I doing wrong? Model attached.

Thanks for your help
Steven

1 Like
6

Hello @stevens

Good to see that you are having progress with your inquiry.

Hi @PankajNamekar,

I wanted to take a moment to thank you for your time and expertise in assisting Steven. We truly appreciate your help. I also noticed that Steven has posted a follow-up question, and we were wondering if you would still be able to provide your valuable insights.

Thank you!

7

Hi everyone, thank you for your help on this subject. I have finally found a solution to the problem after a steer from one of Sam’s Time Diff videos where he shows the power of indexing.

The solution lies in power query. The problem was aligning on off alarm times that are in a single column. Utilising ordinary indexing doesn’t work because each device has its own time sequence. So the problem is creating an index for each individual device.

I found the answer in this great video by Rick de Groot: https://gorilla.bi/power-query/get-previous-row-value/

In Summary: Using power query.

  1. Sort the date alarm column.
  2. Group the device column, selecting Task, Operation = All Rows. This creates a table object
  3. You then apply an index column for each of the tables. Add a custom column using "= Table.AddIndexColumn( Add the table name created in step 2) , “Index”, 1,1,Int64.Type)
  4. Add a column using subtract. Use 1 so you have another index column this time starting with 0.
  5. Merge the two index columns. Also need to match the device time column.

Now the original alarm time and the next alarm time are on the same row ready for time difference analysis.

Again everyone thanks for your supprt.

Steven

1 Like