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 some additional logic that looks for another 'Repair' thats been raised within 14 days in the future, so although the engineer might of completed a job on the 1st appointment, if a new 'Repair' job is raised within 2 weeks this will state 'No', please see example below, the rows highlighted in yellow have all had a new job raised within 14 days in the future and green is the new job thats been raised within 14 days so I want the 'New Job Raised Within 14 Days' to be the measured column that identifies these jobs, the 'client ID' is a unique number for the address

 

Please note that I dont want an additional table created to help with this.

 

 

Here is a pbix example: https://drive.google.com/file/d/1kJLALSZpADrD9l3IQSEyIvfr-iO2bTUD/view?usp=sharing

 

Thanks

 

Daniel

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

11 Replies

  • 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

  • SamWiseOwl's avatar
    SamWiseOwl
    Icon for Super User rankSuper User
    2 years ago

    Hello Daniel_carle ,

    New Repair Job within 14 days =
    var clientID = SELECTEDVALUE(Sheet1[ClientID])--capture current rows client
    var clientDate = SELECTEDVALUE(Sheet1[Date])--capture current rows date
    var filttable =
    FILTER(
        ALLSELECTED(Sheet1)--Remove all filters
        ,Sheet1[Job Category] = "Repair" && Sheet1[Date] > clientdate && Sheet1[Date] <= clientdate + 14&& clientID = clientID)
        --Filter to only repairs that happen after current row and on or before 14 days in the future
    RETURN IF(SELECTEDVALUE(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
     

    Let me know how you get on 🙂