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.

Anonymous's avatar
Anonymous
Not applicable
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.

PMons88's avatar
PMons88
Regular Visitor
4 years ago

Hi Anonymous ,

Thank you so much for the solution. The example you provided is exactly what I was looking for. And you are right about the little error in the data, that was a copy paste mistake when creating the sample data.

The only issue I have got now is that when I try to implement your expression, Power BI is not happy and is running out of memory. It is a table of roughly 500.000 records. Any suggestions how to work around this issue?

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    PMons88 , I'm glad it worked. 

     

    Could I ask that you accept my reply as the solution, please? It helps other members solve similar problems. 

     

    For the performance issue, I would suggest posting it as a separate question with "performance" in the topic title. I am not currently very good at performance-tuning DAX, but I know there are some very knowledgeable members on this forum that would certainly be able to help.