Forum Discussion
uptime
Hi professionals, I have data like this
I need %up time, %down time, uptime hours and downtime hours
Can someone give me ideas please.
Thanks.
Hello ryan_mayu here is the sample data. desired outcome is
1. %up time
2.%downtime
3.up time hours
4.downtime hours
thank you
Name DateTime Duration Status A 1/10/2021 10:29 59 Up A 1/10/2021 10:30 478957 Up A 12/10/2021 22:45 3894 Down A 12/10/2021 23:50 145046 Up B 1/10/2021 10:30 37 Up B 1/10/2021 10:30 479099 Up B 12/10/2021 22:41 4195 Down B 12/10/2021 23:51 144955 Up C 11/10/2021 15:23 37 Up C 11/10/2021 15:24 112805 Up C 12/10/2021 22:44 3895 Down C 12/10/2021 23:49 145159 Up D 11/10/2021 15:24 37 Up D 11/10/2021 15:24 112805 Up D 12/10/2021 22:44 3895 Down D 12/10/2021 23:49 144943 Up Hi, jaipal
here goes your solution, if your Duration is in second:
Four measures:Uptime = 1 - (SUMX(FILTER('Table','Table'[Status]="Down"),'Table'[Duration]) / (SUMX(FILTER('Table','Table'[Status]="Up"),'Table'[Duration]))) Downtime = SUMX(FILTER('Table','Table'[Status]="Down"),'Table'[Duration]) / (SUMX(FILTER('Table','Table'[Status]="Up"),'Table'[Duration])) UptimeHours = (SUMX(FILTER('Table','Table'[Status]="Up"),'Table'[Duration]))/60/60 DowntimeHours = (SUMX(FILTER('Table','Table'[Status]="Down"),'Table'[Duration]))/60/60I am glad it's working, if you could Kudo my original message, so this threat is marked as Solved.
jaipal Many thanks
10 Replies
jaipal , Create a new columns
time in hour = datediff( [Datetime], maxx(filter(Table, [Name] = earlier([Name]) && [Datetime] < earlier([Datetime]) ),[Datetime]) , minute)/60
Last Status = Var _max= maxx(filter(Table, [Name] = earlier([Name]) && [Datetime] < earlier([Datetime]) ),[Datetime])
return
maxx(filter(Table, [Name] = earlier([Name]) && [Datetime] =_max ),[status])Then create measures
Uptime = sumx(filter(Table,[Last Status] ="Up"),[time in hour])
Dwontime = sumx(filter(Table,[Last Status] ="Down"),[time in hour])0, [[Datetime]] )
Hello amitchandak Amit, thank you for your reply, I just added duration to each event, now I needed
1. %up time
2.%downtime
3.up time hours
4.downtime hours
thanks in advance
Name DateTime Duration Status A 1/10/2021 10:29 59 Up A 1/10/2021 10:30 478957 Up A 12/10/2021 22:45 3894 Down A 12/10/2021 23:50 145046 Up B 1/10/2021 10:30 37 Up B 1/10/2021 10:30 479099 Up B 12/10/2021 22:41 4195 Down B 12/10/2021 23:51 144955 Up C 11/10/2021 15:23 37 Up C 11/10/2021 15:24 112805 Up C 12/10/2021 22:44 3895 Down C 12/10/2021 23:49 145159 Up D 11/10/2021 15:24 37 Up D 11/10/2021 15:24 112805 Up D 12/10/2021 22:44 3895 Down D 12/10/2021 23:49 144943 Up vojtechsima thank you, that works
I am glad it's working, if you could Kudo my original message, so this threat is marked as Solved.
jaipal Many thanks
Hello ryan_mayu here is the sample data. desired outcome is
1. %up time
2.%downtime
3.up time hours
4.downtime hours
thank you
Name DateTime Duration Status A 1/10/2021 10:29 59 Up A 1/10/2021 10:30 478957 Up A 12/10/2021 22:45 3894 Down A 12/10/2021 23:50 145046 Up B 1/10/2021 10:30 37 Up B 1/10/2021 10:30 479099 Up B 12/10/2021 22:41 4195 Down B 12/10/2021 23:51 144955 Up C 11/10/2021 15:23 37 Up C 11/10/2021 15:24 112805 Up C 12/10/2021 22:44 3895 Down C 12/10/2021 23:49 145159 Up D 11/10/2021 15:24 37 Up D 11/10/2021 15:24 112805 Up D 12/10/2021 22:44 3895 Down D 12/10/2021 23:49 144943 Up Hi, jaipal
here goes your solution, if your Duration is in second:
Four measures:Uptime = 1 - (SUMX(FILTER('Table','Table'[Status]="Down"),'Table'[Duration]) / (SUMX(FILTER('Table','Table'[Status]="Up"),'Table'[Duration]))) Downtime = SUMX(FILTER('Table','Table'[Status]="Down"),'Table'[Duration]) / (SUMX(FILTER('Table','Table'[Status]="Up"),'Table'[Duration])) UptimeHours = (SUMX(FILTER('Table','Table'[Status]="Up"),'Table'[Duration]))/60/60 DowntimeHours = (SUMX(FILTER('Table','Table'[Status]="Down"),'Table'[Duration]))/60/60
