Forum Discussion
PMons88
4 years agoRegular Visitor
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...
- Anonymous4 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 vFirstChangedDateWhen 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
4 years agoRegular Visitor
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
4 years agoNot applicable
PMons88 , yes sure.