Calculate Total time duration for ON/OFF states

[Anonymous]

Hello All,

I have a table where it got date values with every three minutes of intervel.

In that table itself i have a column which gives status of AC whether its ON/OFF.

See the table below.

Time	AC Status
11:03:00 AM	ON
11:06:00 AM	OFF
11:09:00 AM	ON
11:12:00 AM	ON
11:15:00 AM	ON
11:18:00 AM	ON
11:21:00 AM	OFF
11:24:00 AM	OFF
11:27:00 AM	ON
11:30:00 AM	ON
11:33:00 AM	ON
11:36:00 AM	ON
11:39:00 AM	ON
11:42:00 AM	ON
11:45:00 AM	OFF
11:48:00 AM	OFF
11:51:00 AM	OFF
11:54:00 AM	ON
11:57:00 AM	ON
12:00:00 PM	ON

Now i would like to calclute the time duration in minutes for the both states that is ON/OFF.

i.e total running time duration for ON status, i.e total running time duration for OFF status,

Can any one please suggest me to get this done.

I will be so thankful to you.

Thanks,

Mohan V

[CanadaMGB]

Assuming your interval is always 3 minutes, you can count the rows in each status, subtract the status count of the first record, and multiple by 3. Try this:

ON TEST = 

VAR 
MINDATE = MIN(Table1[time])
VAR
MINCOUNT = COUNTROWS(filter(Table1,Table1[AC Status]="on" && Table1[time] = MINDATE))
VAR
ONCOUNT = COUNTROWS(filter(Table1,Table1[AC Status]="on"))
RETURN
(ONCOUNT-MINCOUNT)*3

If you do want to count the result of the first row, just use the ONCOUNT= line and skip the rest. 

[danextian]

Hi @Anonymous,

Are you trying to get the time difference between the current row and the previous row?