Staggered function MTTB and MTTR

[josevarelac]

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.

[v-eachen-msft]

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]

@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:

Fip	09-09-2019
Ftp	11-09-2019
Machine	Detention Date	Repair Date
A	10-09-2019 14:20	12-09-2019 15:30
A	15-10-2019 10:32	20-10-2019 4:30
A	11-11-2019 11:11	11-11-2019 12:30
B	12-07-2019 12:45	14-07-2019 12:56
B	18-09-2019 12:01	01-10-2019 0:00
B	02-10-2019 12:34	10-10-2019 4:50
B	12-11-2019 17:50	15-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 · Accepted solution]

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.

[Greg_Deckler]

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/3395...

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

[josevarelac]

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.