Forum Discussion

josevarelac's avatar
josevarelac
Frequent Visitor
6 years ago
Solved

Staggered function MTTB and MTTR

 

I come to you with the following challenge. I need to do a KPI on Power BI related to maintenance sector -calculate the Availability of certain machine -. The formula to calculate is the following one:

Availability (fip, ftp) = Working time in period fip-ftp /Total Operating Time, where:fip = initial date of evaluation, ftp = final date of evaluation.

Working Time = Total Operating Time in period fip - ftp - Downtime in period fip - ftp.
Total Operatinal Time = Working Time of Machine in a day * (ftp-fip)
(ftp-fip) = delta time of evaluation

As you can see i need to see the Availability between fip and ftp, be available to "play" with this date (it will be great to do using a slider in Power Bi, and the KPI changes depending the dates).

Soo, in order to do this i have the date in this format:

 

For example, if i want to get the KPI for Machine A and B for:fip = 01/07/2019 and ftp=20/12/2019 you will notice that fip date selected happens before Detention Date and ftp is before Start Date, soo the Availability for each maquine it will be:

 

As you notice you will have multiple cases, but the case asume that if a machine has a failure it can´t operate until is fixed. The cases that i need to program are the following:

A) Detention Date <fip and Start Date <fip, that a means that the time down is 0.

B) Detention Date <fip and Start Date >fip, that a means that the time down is Start Date - fip.

C) Detention Date >fip and Start Date <ftp, that a means that the time down is Start Date - Detentión Date.

D) Detention Date >fip and Start Date >ftp, that a means that the time down is Start Date - ftp.

I need to program in power bi with the idea of using a slicer so the user can see de Availability on any period of time. Any help is gratefully appreciated.

Need Help
Tips and Tricks
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    Hi josevarelac ,

     

    I understood your requirements. There is the new codes for your reference.

    Measure =
SWITCH (
    TRUE (),
    [fip] > SELECTEDVALUE ( 'Table'[Repair Date] ), 0,
    [fip] < SELECTEDVALUE ( 'Table'[Repair Date] )
        && [ftp] > SELECTEDVALUE ( 'Table'[Repair Date] )
        && [fip] > SELECTEDVALUE ( 'Table'[Detention Date] ), "start date-fip",
    [fip] < SELECTEDVALUE ( 'Table'[Detention Date] )
        && [ftp] > SELECTEDVALUE ( 'Table'[Repair Date] ), "start date-detention date",
    [fip] < SELECTEDVALUE ( 'Table'[Detention Date] )
        && [ftp] > SELECTEDVALUE ( 'Table'[Detention Date] )
        && [ftp] < SELECTEDVALUE ( 'Table'[Repair Date] ), "start date-ftp"
)

    Of course you still need to replace the result with the filters you want.

     

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490.

     

    That being said, See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586

     

    Not sure if it will be helpful without example data and example of your expected output.

    • josevarelac's avatar
      josevarelac
      Frequent Visitor
      6 years ago

      Thnk, i´ve all ready seen your post and help me a lot. But i have problems when i want to change the evaluation period. I want to be able to change the horizon of evaluation, and as i change i will have different Availability.

  • v-eachen-msft's avatar
    v-eachen-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi josevarelac ,

     

    Assuming your DAX looks like this

    Your DAX =
CALCULATE([Your Expression],Your filter)

    You need to use SWITCH() or IF() function to ensure your filter formula.

    Your filter =
VAR StartDate =
    CALCULATE ( MAX ( 'Table'[Date] ), ALLSELECTED ( 'Table'[Date] ) )
VAR DetentionDate =
    CALCULATE ( MIN ( 'Table'[Date] ), ALLSELECTED ( 'Table'[Date] ) )
VAR FORMULA =
    SWITCH (
        TRUE (),
        [fip] > StartDate, 0,
        [fip] < StartDate
            && [ftp] > StartDate
            && [fip] > DetentionDate, "start date-fip",
        [fip] < DetentionDate
            && [ftp] > StartDate, "start date-detention date",
        [fip] < DetentionDate
            && [ftp] > DetentionDate
            && [ftp] < StartDate, "start date-ftp"
    )
RETURN
    FORMULA

    You need to replace the result with the filters you want. For example:

    FILTER('Table','Table'[Date]>[fip]&&'Table'[Date]<[ftp])

    Here is my test file for your reference.

     

    • josevarelac's avatar
      josevarelac
      Frequent Visitor
      6 years ago

      v-eachen-msft First, thanks for your information. But i must be able to compare fip and ftp with tha data in the table, you can use this example:

      Fip09-09-2019 
      Ftp11-09-2019 
         
      MachineDetention DateRepair Date
      A10-09-2019 14:2012-09-2019 15:30
      A15-10-2019 10:3220-10-2019 4:30
      A11-11-2019 11:1111-11-2019 12:30
      B12-07-2019 12:4514-07-2019 12:56
      B18-09-2019 12:0101-10-2019 0:00
      B02-10-2019 12:3410-10-2019 4:50
      B12-11-2019 17:5015-11-2019 0:00

       

      So in this case StartDate and Detención date will in the table, but the code that you have doesn´t allow to compare fip and start date or ftp and detention date. 

      • v-eachen-msft's avatar
        v-eachen-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi josevarelac ,

         

        I understood your requirements. There is the new codes for your reference.

        Measure =
SWITCH (
    TRUE (),
    [fip] > SELECTEDVALUE ( 'Table'[Repair Date] ), 0,
    [fip] < SELECTEDVALUE ( 'Table'[Repair Date] )
        && [ftp] > SELECTEDVALUE ( 'Table'[Repair Date] )
        && [fip] > SELECTEDVALUE ( 'Table'[Detention Date] ), "start date-fip",
    [fip] < SELECTEDVALUE ( 'Table'[Detention Date] )
        && [ftp] > SELECTEDVALUE ( 'Table'[Repair Date] ), "start date-detention date",
    [fip] < SELECTEDVALUE ( 'Table'[Detention Date] )
        && [ftp] > SELECTEDVALUE ( 'Table'[Detention Date] )
        && [ftp] < SELECTEDVALUE ( 'Table'[Repair Date] ), "start date-ftp"
)

        Of course you still need to replace the result with the filters you want.