cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LenaPowerBI Frequent Visitor
Frequent Visitor

First time fix

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

 

 

3 REPLIES 3
LenaPowerBI Frequent Visitor
Frequent Visitor

Re: First time fix

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

Community Support Team
Community Support Team

Re: First time fix

hi, @LenaPowerBI 

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:

5.JPG

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
LenaPowerBI Frequent Visitor
Frequent Visitor

Re: First time fix

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

Helpful resources

Announcements
New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,838)