First time fix

[Anonymous]

Hello,

I am new to PowerBI and I'd like to ask for a help with creating the following DAX function. Data set example:

Installation number        Service ticket number         ST date created         ST date closed

1234                                123313                               1/1/2019                     2/1/2019 

                                        123545                               3/1/2019                     3/1/2019

                                        123333                               1/9/2019                     2/9/2019

For each installation numbers we have service ticket(s). 

I want to measure first time fix understood as: If given service ticket was not followed up by another one for the same installation number in the period of 90 days, then we consider this service ticket to fall into first time fixed. So the service ticket 123545 was closed in January and the next one was in Sept, ergo it is First time fixed, whereas the 123313 service ticket, had a later one created just the next day, ergo it is not first time fixed. Could you please help with how to create this query?

Thank you

Lena

[king98027]

Did you ever get this to work? I'm in the same predicament. I tried using the formula but am getting bogus values. 

Here was my separate thread:

https://community.powerbi.com/t5/Desktop/First-Time-Fix-Rate/m-p/2478575#M883071

[v-lili6-msft]

hi, @Anonymous 

You could try this formula to get your requirement:

Measure 2 =
VAR nextcreatedate =
    CALCULATE (
        MIN ( 'Table'[ST date created] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Installation number] = MAX ( 'Table'[Installation number] )
                && MAX ( 'Table'[ST date closed] ) < 'Table'[ST date created]
        )
    )
RETURN
    VAR days =
        IF (
            nextcreatedate = BLANK (),
            DATEDIFF ( MAX ( 'Table'[ST date closed] ), TODAY (), DAY ),
            DATEDIFF ( MAX ( 'Table'[ST date closed] ), nextcreatedate, DAY )
        )
    RETURN
        IF ( days >= 90, "First time", "No" )

Result:

Best Regards,

Lin

[SAU1111]

How to create a First time fix %

[Anonymous]

I should add, that if a service ticket was closed in say May this year, but no new service ticket was created until today, then that ticket should also be counted as First time Fixed.

Thanks, Lena

[Anonymous]

Thank you very much Lin, it works great on a row level. However, the measure is formatted as text,  (I have changed 'First time fix =1, and 'No" =0) to make it countable, I also added +0 at the end of the measure to be able to format it as whole number. I still am unable to sum up the results, and total appears as 1. Can you please help me to modify it so that I can get totals for specific time frames? I need to measure this KPI every month.

Thank you,

Lena

First time fix =

VAR nextcreatedate =

CALCULATE (

MIN ( Services[Date Created] ),

FILTER (

ALLSELECTED ( 'Services' ),'Services'[Installation] = MAX ( 'Services'[Installation] )

&& MAX ('Services'[Date Closed] ) < 'Services'[Date Created]

)

)

RETURN

VAR days =

IF (

nextcreatedate = BLANK (),

DATEDIFF ( MAX ( 'Services'[Date Closed] ), TODAY (), DAY ),

DATEDIFF ( MAX ( 'Services'[Date Closed] ), nextcreatedate, DAY )

)

RETURN

IF ( days >= 90, "1", "0" )+0