Forum Discussion

jaipal's avatar
jaipal
Resolver III
4 years ago
Solved

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.
Need Help
Tips and Tricks
  • jaipal's avatar
    jaipal
    4 years ago

    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

    NameDateTimeDurationStatus
    A1/10/2021 10:2959Up
    A1/10/2021 10:30478957Up
    A12/10/2021 22:453894Down
    A12/10/2021 23:50145046Up
    B1/10/2021 10:3037Up
    B1/10/2021 10:30479099Up
    B12/10/2021 22:414195Down
    B12/10/2021 23:51144955Up
    C11/10/2021 15:2337Up
    C11/10/2021 15:24112805Up
    C12/10/2021 22:443895Down
    C12/10/2021 23:49145159Up
    D11/10/2021 15:2437Up
    D11/10/2021 15:24112805Up
    D12/10/2021 22:443895Down
    D12/10/2021 23:49144943Up
  • vojtechsima's avatar
    vojtechsima
    4 years ago

    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

     

  • vojtechsima's avatar
    vojtechsima
    4 years ago

    I am glad it's working, if you could Kudo my original message, so this threat is marked as Solved.
    jaipal Many thanks

     
jaipal's avatar
jaipal
Resolver III
4 years ago

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

NameDateTimeDurationStatus
A1/10/2021 10:2959Up
A1/10/2021 10:30478957Up
A12/10/2021 22:453894Down
A12/10/2021 23:50145046Up
B1/10/2021 10:3037Up
B1/10/2021 10:30479099Up
B12/10/2021 22:414195Down
B12/10/2021 23:51144955Up
C11/10/2021 15:2337Up
C11/10/2021 15:24112805Up
C12/10/2021 22:443895Down
C12/10/2021 23:49145159Up
D11/10/2021 15:2437Up
D11/10/2021 15:24112805Up
D12/10/2021 22:443895Down
D12/10/2021 23:49144943Up
ryan_mayu's avatar
ryan_mayu
Super User
4 years ago

jaipal 

do you want to calculate overall %up/ down time? or the percentage for each name?

how to calculate the hours?Using the duration? is the duration seconds or minutes? 

  • jaipal's avatar
    jaipal
    Resolver III
    4 years ago

    ryan_mayu 

    I need overall %uptime and %downtime for month. and each name as well

    and need to calculate duration based on duration, and yes duration is in seconds. 

    • vojtechsima's avatar
      vojtechsima
      Super User
      4 years ago

       

       

      Uptime = 
var Uptime = CALCULATE(
    1 - (SUMX(FILTER('Table','Table'[Status]="Down"),'Table'[Duration]) / (SUMX(FILTER('Table','Table'[Status]="Up"),'Table'[Duration]))),
    FILTER('Table','Table'[DateTime].[MonthNo]<=MAX('Table'[DateTime].[MonthNo]))
)

var Check = IF(ISBLANK(Uptime),1, Uptime)

Return Check

Downtime = 
var Downtime = CALCULATE(
    SUMX(FILTER('Table','Table'[Status]="Down"),'Table'[Duration]) / (SUMX(FILTER('Table','Table'[Status]="Up"),'Table'[Duration])),
    FILTER('Table','Table'[DateTime].[MonthNo]<=MAX('Table'[DateTime].[MonthNo]))
)
var Check = IF(ISBLANK(Downtime),0, Downtime)

Return Check


UptimeHours = CALCULATE(
    (SUMX(FILTER('Table','Table'[Status]="Up"),'Table'[Duration])/60/60),
    FILTER('Table','Table'[DateTime].[MonthNo]<=MAX('Table'[DateTime].[MonthNo])))

DowntimeHours = CALCULATE((SUMX(FILTER('Table','Table'[Status]="Down"),'Table'[Duration]))/60/60,
FILTER('Table','Table'[DateTime].[MonthNo]<=MAX('Table'[DateTime].[MonthNo])))

      jaipal