Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 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 prod...
Calculated columns
column calculation
DAX Formulas
  • v-jianboli-msft's avatar
    v-jianboli-msft
    4 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.

     

v-jianboli-msft's avatar
v-jianboli-msft
Icon for Community Support rankCommunity Support
4 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
    4 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?