Structure data so that it shows Start and End Dates based on a date and status column

[rush]

I have a table that shows staff's employment status changes with an effective date. I am trying to create 2 columns from it being the start and end date based on the Employment Status.

*It needs to ignore dates where a staff moves from one type of Employment Status excluding Terminated.

Please see an example of the data and how I would like it to end up being in Power BI.

Thank you very much in advance.

Example

[Anonymous]

Hi @rush ,

Based on the screenshot, the End Date is each of date when the Employment Status="Terminated", then the Start Date is the each earliest date before Employment Status="Terminated".

But what's the logic of the new Employment Status? 

According to my understanding,  it may be the every first status before Employment Status="Terminated", which is different from yours:

My data sample:

Below is my method

1. Add a flag column to specific each Terminated:

Flag = RANKX(FILTER('Table',[EEID]=EARLIER('Table'[EEID]) && [Employee Code]=EARLIER('Table'[Employee Code]) && [Employment Status]="Terminated" ),[Effective Date],,ASC,Dense)

 2. Create a new table:

New Table = SUMMARIZE('Table',[EEID],[Employee Code],[Full Name],[Employee Status],[Contract Type],[Practice],[Flag],"Start Date", MIN('Table'[Effective Date]),"End Date",MAX('Table'[Effective Date]))

3. Find the new Employment Status:

Employeement Status = LOOKUPVALUE('Table'[Employment Status],'Table'[EEID],[EEID],'Table'[Employee Code],[Employee Code],'Table'[Effective Date],[Start Date])

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rush]

@Anonymous Thanks once again for the possible solution but I noticed the DAX is not working 100% as I picked up some staff that are active with 1 record but have an end date which should not be the case.

Some staff will have multiple employment changes that do not contain terminated and should not have an end date which is happening in this case.

[rush]

@Anonymous Thank you very much. It works well but if someone's employee status is Active then their End Date should be blank and not equal to their start date whereby I can create a column to resolve that issue.

The logic of the new employment status is for historical purposes as I am wanting to use this table to create a staff headcount table so I can count staff that has left and come back multiple times and for starters and leavers.

I have power query formula that I used to calculate headcount on a daily basis and then summarised to a monthly basis but now I cannot use that as I am using the created table with DAX.

What would the DAX equivalent formula look like for the below:

{ Number.From([Start Date]).. Number.From(

if [#"End Date - Future Dated for Nulls"] = null
then Date.From(DateTime.LocalNow())
else [#"End Date - Future Dated for Nulls"])

}