Forum Discussion

Daniel_carle's avatar
Daniel_carle
Icon for Helper I rankHelper I
2 years ago
Solved

First Time Fix (New Job Raised Within 14 days)

Hello,   I need to be able to to calculate first time fix but with an additional check please, I've been able to identify 'Repair' jobs that have been completed first time but my organisation want ...
Need Help
  • SamWiseOwl's avatar
    SamWiseOwl
    2 years ago

    COLUMN Repairwithin =
        VAR filttable =
            FILTER (
                Sheet1 --Remove all filters
                ,
                Sheet1[Job Category] = "Repair"
                    && Sheet1[Date] > EARLIER ( Sheet1[Date] )
                    && Sheet1[Date]
                        <= EARLIER ( Sheet1[Date] ) + 14
                    && Sheet1[ClientID] = EARLIER ( Sheet1[ClientID] )
            ) --Filter to only repairs that happen after current row and on or before 14 days in the future
        RETURN
            IF (
                Sheet1[Job Category] = "Repair"
                    && COUNTROWS ( filttable ) > 0,
                "Yes",
                "No"
            )
    --If the current row is a repair and the jobs is greater than 0 return Yes

bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
2 years ago

Daniel_carle , To achieve this create a new calculated column using 

 

New Job Raised Within 14 Days =
VAR CurrentClientID = 'YourTable'[Client ID]
VAR CurrentJobDate = 'YourTable'[Job Date]
RETURN
IF (
COUNTROWS (
FILTER (
'YourTable',
'YourTable'[Client ID] = CurrentClientID &&
'YourTable'[Job Date] > CurrentJobDate &&
'YourTable'[Job Date] <= CurrentJobDate + 14
)
) > 0,
"No",
"Yes"
)

Daniel_carle's avatar
Daniel_carle
Icon for Helper I rankHelper I
2 years ago

Hello bhanu_gautam ,

 

Thanks for the quick reply, unfortunetly this hasnt worked, see below results, the 'New Job Raised Within 2 Weeks' should match 'New job Raised Within 14 Days', also this is only for job category 'Repair' please

 

 

Thanks

 

Daniel