Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

First time right for repairs

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!
Calculated columns
column calculation
DAX Formulas
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    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.

     

8 Replies

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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?

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      Thanks!!!😀

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Rvd 18,

    Do you have a D&ate table ?

    And could you provide some datas ?

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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!

    Serialnumberreportingdateclosingdate
    EX010058F27-2-202027-2-2020
    EX0101HS729-9-202029-9-2020
    EX0101HWH29-9-202029-9-2020
    EX010514221-12-202023-12-2020
    EX0105HNZ12-11-202012-11-2020
    EX0105HNZ29-7-202029-7-2020
    EX0105HP612-11-202012-11-2020
    EX0105HPQ26-10-202127-10-2021
    EX0107TDZ11-10-202113-10-2021
    EX0107TE113-11-202013-11-2020
    EX0109ZK016-9-202017-9-2020
    EX010FNP9:28-7-202028-7-2020
    EX010FNX012-11-202012-11-2020
    EX010GGT713-8-202013-8-2020
    EX010GGT74-8-20205-8-2020
    EX010GGTC19-8-202222-8-2022
    EX010GGTJ19-8-202222-8-2022
    EX010GGTS28-7-202028-7-2020
    EX010HDS612-11-202012-11-2020
    EX010HDS629-7-202029-7-2020
    EX010HDSP12-11-202012-11-2020
    EX010JNT322-8-202222-8-2022
    EX010LFKV10-8-202210-8-2022
    EX010LJYT13-4-202213-4-2022
    EX010M46125-3-202229-3-2022
    EX010M46125-8-202127-8-2021
    EX010M49Q28-2-202228-2-2022
    EX010M4GC20-5-202220-5-2022
      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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!