Forum Discussion

PMons88's avatar
PMons88
Regular Visitor
4 years ago
Solved

Retrieving the date when a value changes for current and following rows

Good afternoon, I am working with a dataset that contains staff data and has snapshot data in it. For each month a person is employed, a records is created with a date representing that month [Pro...
  • Anonymous's avatar
    Anonymous
    4 years ago

    PMons88 , try this calculated column:

    DESIRED OUTCOME = 
    VAR vJobCat = 'Table'[JOB CATEGORY]
    VAR vDate = 'Table'[DATE]
    VAR vEmpID = 'Table'[EMP_ID]
    VAR vLastDateWhenJobCatWasDifferent =
        CALCULATE (
            MAX ( 'Table'[DATE] ),
            FILTER (
                'Table',
                'Table'[DATE] < vDate
                    && 'Table'[JOB CATEGORY] <> vJobCat
                    && 'Table'[EMP_ID] = vEmpID
            )
        )
    VAR vFirstChangedDate =
        CALCULATE (
            MIN ( 'Table'[DATE] ),
            FILTER (
                'Table',
                'Table'[DATE] > vLastDateWhenJobCatWasDifferent
                    && 'Table'[EMP_ID] = vEmpID
            )
        )
    RETURN
        vFirstChangedDate

    When I try it with your sample data it gives these results:

    By the way, it looks like the DESIRED OUTCOME column is wrong for months Apr-22 and May-22. When the Job Category changes from AAA to CCC on 1 Apr-22, I believe you want the new column to show 1 Apr-22 for rows with the date is 1 Apr-22 and 1 May-22.