Column Function To Evaluate Current Row Against Later Row (Next Period)

[BrianVT]

The following is sample data and would have rows with several years of monthly pulls of a roster and what Org (think department) every employee is in as of the end of each month.  I want to create a column (shown as "Status Change") that evaluates each row against that same person in the next month to see if their Org changed or if they disappeared completely from the list.  "Re-Alignment" would mean that their Org changes in the next period and "Departed" means it was their last month with the company since they were not on the roster in the next period.  Blanks can be given for everything else.

File Date	Name	Org Tier 1	Status Change
10/31/2018	Smith, John	4.0
11/30/2018	Smith, John	4.0
12/31/2018	Smith, John	4.0
1/31/2019	Smith, John	4.0
2/28/2019	Smith, John	4.0
3/31/2019	Smith, John	4.0
4/30/2019	Smith, John	4.0
5/31/2019	Smith, John	4.0	Re-alignment
6/30/2019	Smith, John	8.0
7/31/2019	Smith, John	8.0
8/31/2019	Smith, John	8.0
9/30/2019	Smith, John	8.0
10/31/2019	Smith, John	8.0
11/30/2019	Smith, John	8.0	Departed

[ryan_mayu · Accepted solution]

@BrianVT 

pls try to create a column

Column = 
var org= MINX(FILTER(Sheet1,Sheet1[Name]=EARLIER(Sheet1[Name])&&'Sheet1'[File Date]>EARLIER(Sheet1[File Date])),Sheet1[Org Tier 1])
var _date=MAXX(FILTER(Sheet1,Sheet1[Name]=EARLIER(Sheet1[Name])),Sheet1[File Date])
return if(_date=Sheet1[File Date],"Departed",if(org=Sheet1[Org Tier 1],"","Re-alignment"))

[BrianVT]

Thanks, Ryan - So that column function includes two measures that I need to create first?

[ryan_mayu]

@BrianVT 

That's one measure of column.

you can learn more about VAR function here

https://docs.microsoft.com/en-us/dax/var-dax

[BrianVT]

Wow, that's amazing.  I've never used thosed within column functions before.  Thank you very much!