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

     
ryan_mayu's avatar
ryan_mayu
Super User
4 years ago

jaipal 

could you pls provide the data in the table not the screenshot? What's more, pls provide the expected output based on your sample data.

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
  • vojtechsima's avatar
    vojtechsima
    Super User
    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

     

  • 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