First time right for repairs

[Anonymous]

Hello!

I'm trying to write a formula for a measured column to calculate wheter or not a repair was right at the first attempt. I have a table with tickets and columns for

- serialnumbers of products,

- dates on which an issue was reported, and

- dates on which an issue was solved.

My formula should aim to count the number of times a serial number occurs from the closing date, until 30 days later. If this is zero times, then the repairs is considered a first time right. If not (and a product malfunction is reported again for the same serial number within 30 days) then obviously it's not first time right. I can't seem to figure out how to write this IF statement.

My current attempt for a calculated column looks like this (but doesn't give the result i'm looking for):

First time right= IF( CALCULATE( COUNT( Tickets[Serialnumber]), DATESBETWEEN(Tickets[reportingdate], Tickets[closingdate], Tickets[closingdate] +30)) = 0 THEN "'Firsttimeright", "Not firsttimeright")

Any help is appreciated. If you have any questions please let me know!

[v-jianboli-msft · Accepted solution]

Hi @Anonymous ,

Please try:

Column =
VAR _a =
    CALCULATE (
        COUNT ( 'Table'[Serialnumber] ),
        FILTER ( 'Table', [Serialnumber] = EARLIER ( 'Table'[Serialnumber] ) ),
        DATESINPERIOD ( 'Table'[closingdate], 'Table'[closingdate], 30, DAY )
    )
VAR _b =
    CALCULATE (
        COUNT ( 'Table'[Serialnumber] ),
        FILTER (
            'Table',
            [Serialnumber] = EARLIER ( 'Table'[Serialnumber] )
                && (
                    (
                        [closingdate] >= EARLIER ( 'Table'[closingdate] )
                            && [closingdate]
                                <= EARLIER ( 'Table'[closingdate] ) + 30
                    )
                        || (
                            [reportingdate] <= EARLIER ( 'Table'[reportingdate] )
                                && [reportingdate]
                                    >= EARLIER ( 'Table'[reportingdate] ) - 30
                        )
                )
        )
    )
RETURN
    IF (
        _b = 1,
        "first time right",
        IF ( _a = 1, "right(not first time) ", "not right" )
    )

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks!!!😀

[v-jianboli-msft]

Hi @Anonymous ,

Please try:

First time right for repairs =
VAR _a =
    CALCULATE (
        COUNT ( 'Table'[Serialnumber] ),
        FILTER ( 'Table', [Serialnumber] = EARLIER ( 'Table'[Serialnumber] ) ),
        DATESINPERIOD ( 'Table'[closingdate], 'Table'[closingdate], 30, DAY )
    )
RETURN
    IF ( _a = 1, "first time right", "not first time right" )

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Yes this works! Thank you 🙂 

I have an additional question.. in case of multiple repairs, the final repair will always show a first time right as the formula only looks at the future, but this is misleading. Can we build it so that the count function looks for the same serial numbers 30 days after the solve date and 30 days before the reportingdate to solve this?

[Anonymous]

Hi JamesFr06,

I have a date table linked to the reportingdate column. Please find a part of the table with some examples below. I included serialnumbers that cover all 3 possibilities (serial number occurs once -> first time right, serial number occurs twice within 30 days -> not first time right, serial number occers multiple times but with intervals greater than 30 days -> first time right). Hope this helps. Thanks for your effort!

Serialnumber	reportingdate	closingdate
EX010058F	27-2-2020	27-2-2020
EX0101HS7	29-9-2020	29-9-2020
EX0101HWH	29-9-2020	29-9-2020
EX0105142	21-12-2020	23-12-2020
EX0105HNZ	12-11-2020	12-11-2020
EX0105HNZ	29-7-2020	29-7-2020
EX0105HP6	12-11-2020	12-11-2020
EX0105HPQ	26-10-2021	27-10-2021
EX0107TDZ	11-10-2021	13-10-2021
EX0107TE1	13-11-2020	13-11-2020
EX0109ZK0	16-9-2020	17-9-2020
EX010FNP9:	28-7-2020	28-7-2020
EX010FNX0	12-11-2020	12-11-2020
EX010GGT7	13-8-2020	13-8-2020
EX010GGT7	4-8-2020	5-8-2020
EX010GGTC	19-8-2022	22-8-2022
EX010GGTJ	19-8-2022	22-8-2022
EX010GGTS	28-7-2020	28-7-2020
EX010HDS6	12-11-2020	12-11-2020
EX010HDS6	29-7-2020	29-7-2020
EX010HDSP	12-11-2020	12-11-2020
EX010JNT3	22-8-2022	22-8-2022
EX010LFKV	10-8-2022	10-8-2022
EX010LJYT	13-4-2022	13-4-2022
EX010M461	25-3-2022	29-3-2022
EX010M461	25-8-2021	27-8-2021
EX010M49Q	28-2-2022	28-2-2022
EX010M4GC	20-5-2022	20-5-2022

[Anonymous]

Hi Rvd 18,

Tell me if it is ok for you

[Anonymous]

Hi JamesFr06,

Thanks for your effort! This does work in the example but when I apply it to my dataset it doesn't give the correct values anymore... i've tried the formula proposed by Jianbo Li and this worked!

[Anonymous]

Hi Rvd 18,

Do you have a D&ate table ?

And could you provide some datas ?