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

[PMons88]

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 [Profile date].

What I am trying to achieve is to retrieve the Profile date everytime their Job category changes. If the Job category is unchanged it should just take the same date as the previous record.

I have tried the following expression, which does not generate the desired results:

First_Date_Job_Category =

MINX(

  FILTER(

   'Table', EARLIER('Table'[Emp_ID]) = 'Table'[Emp_ID] &&

   EARLIER('Table'[Job Category]) = 'Table'[Job Category]

),

'Table'[Profile date])

What I am getting back is the following: 

If you look at job category AAA (bold), you see that it just takes the first [Profile date] 'AAA' appeared for that Emp_ID. However, this is not what I would like to achieve... What I would like to achieve is that every time the job category changes it takes that date and insert that for any subsequent rows until another Job Category value appears. 

So, as can be seen in the image, for Job category 'AAA', I want the date to change every time someone moves from one of the other job categories into 'AAA'.

I would really appreciate if someone could assist me, as I am really stuck.

Many thanks

[PMons88]

@Anonymous 

Hi again,

I managed to overcome the out of memory issue by reducing my dataset beforehand with some custom SQL. 

However, when implementing your solution I get some strange results back. I am not sure what it is doing but for sometimes it will give the expected results, but most of the times it won't.

Would you be happy to have another look if I shared an anonymised dataset?

[Anonymous]

@PMons88 , yes sure.

[Anonymous · Accepted solution]

@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]

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]

@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.