Add column from third column with "latest date" based on second column value

[S_N_L_A]

Hi all!

I have a bit of a pickle that I hope I can get help with!

I'm going to use a SCD to ensure my report can take into account organisational changes and moves.

Once I finalised my report - I realised that my start date shows Organisational start date, regardless of employees moving onto a new division. How can I get a column that shows the "division start date"?

I.e. How can I show that in below table, Carl's start date in Division A is 2017-01-01 as seen in the report. But Start date for Division B is the end date from his Division A row.

Or like my example with Lisa. Her start date for Division D should be her end date for Division C, and her start date for Division G should be her end date from Division D

Index	Division	Start date	End date	Name	ID sale
1	A	2017-01-01	2019-01-01	Carl	XXX
2	B	2017-01-01	2022-01-01	Carl	XXX
3	C	2000-01-01	2018-05- 05	Lisa	VVV
4	D	2000-01-01	2020-03-03	Lisa	VVV
5	G	2000-01-01	2022-03-29	Lisa	VVV

[S_N_L_A]

Thanks for your help! With this solution though, there are two unwanted issues.

1.  It gave inconsistent outcomes as it does not connect the dates chronologically. Meaning instead of an outcome similar to 2000-2018, 2018-2020, 2020-2022 it gives a column with 2000-2018, 2022-2020, 2022-2020.  Example from my data:

Index	Division	Start date	End date	Name	ID sale	Division Start date (New)
3	C	2000-01-01	2018-05- 05	Lisa	VVV	2000-01-01
4	D	2000-01-01	2020-03-03	Lisa	VVV	2022-03-29
5	G	2000-01-01	2022-03-29	Lisa	VVV	2020-03-03

2. A few employees have left and then come back to the organisation. In these scenarios I would want the start date to be taken from the actual start date column rather than from end date. This is how it shows now 

Index	Division	Start date	End date	Name	ID sale	Division Start date (New)
1	A	1994-01-01	2019-04-30	John	VVV	1994-01-01
2	B	2021-01-01	2022-01-01	John	VVV	2019-04-30

+ an extra: It also adds the last end date for an employee as a start date, which it should not do 🙂 

Thanks!

[johnt75 · Accepted solution]

The code is relying on the index column to be in ascending chronological order, and does give the correct results I think with the sample data

If that's not the case then it could be adapted to rely on the end date column instead, both for filtering out only those rows prior to the current one and for ordering the rows for the TOPN. That would also clear up the problem of adding the last end date as a start date.

To solve the 2nd problem, you could add another filter into the CALCULATETABLE so that it would be

CALCULATETABLE('Table', ALLEXCEPT('Table','Table'[Name]),'Table'[Index] < currentIndex, 'Table'[Start date] = currentStartDate)

[S_N_L_A]

This worked perfectly! Relying on the end date column that is.

Now I'm realising I've got other challenges in my model - but this will definitely help out once I've fixed it.

Thanks!

[johnt75]

You can add a new column like

Division Start Date = 
var currentIndex = 'Table'[Index]
var currentStartDate = 'Table'[Start date]
var prevEndDate = SELECTCOLUMNS( 
    TOPN(1, CALCULATETABLE('Table', ALLEXCEPT('Table','Table'[Name]),'Table'[Index] < currentIndex), 'Table'[Index], DESC), 
    "@val", 'Table'[End date]
)
return IF( NOT( ISBLANK( prevEndDate)), prevEndDate, currentStartDate)