Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
Hi @Anonymous,
Are you trying to get the time difference between the current row and the previous row?
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |