Duplicate Data entries

[O-K]

Employees' ID have multiple entries (duplicated) sometimes more than once in the start date Ex.:

Emp ID	Dep	Start date	End date	Status
23232A	C	12/12/1999	12/12/2001	Active
23232A	B	12/12/1999	31/12/2020	Active
23232A	C	12/12/1999	02/05/2024	Active

1-  I would like to adjust the start date to be the end date of the previous (Latest) one and so.

2- keeping only one Active status per employee (Latest)

[DataNinja777 · Accepted solution]

Hi @O-K 

You can get your required output as follows:

1-  I would like to adjust the start date to be the end date of the previous (Latest) one and so.

2- keeping only one Active status per employee (Latest)

In addition to this, you can create a disconnected calendar table to do a flexible headcount analysis, but I didn't create a calendar table in this example. 

I attach an example pbix file.  

[O-K · Accepted solution]

Firstly thank you for the quick response.
I somehow keep getting an error in the second half

[DataNinja777]

Hi @O-K 

It seems that your formula is missing close bracket for max formula.  Please look at the braket below the star mark in the screen shot.  

Best regards,

[O-K]

Hi @DataNinja777 
The error has now shifted to the Filter formula.

[DataNinja777]

Hi @O-K 

What about removing the extra braket which is red underlined below?

Best regards,

[Khushidesai0109]

Hii Please use this DAX copy and paste

A , is missing after max()

Adjusted Department StartDate =
VAR Departmentstartdate =
CALCULATE(
MAX('EU HR'[Adjusted End/present day]),
FILTER(
'EU HR',
'EU HR'[Adjusted End/present day] < EARLIER('EU HR'[Adjusted End/present day])
)
)
RETURN
IF(
ISBLANK(Departmentstartdate),
'EU HR'[Adjusted End/present day],
Departmentstartdate
)

If this solution helped you please give a thumbs up and accept this reply as a solution Thank You!! Regards!!  

[DataNinja777 · Accepted solution]

Hi @O-K 

You can get your required output as follows:

1-  I would like to adjust the start date to be the end date of the previous (Latest) one and so.

2- keeping only one Active status per employee (Latest)

In addition to this, you can create a disconnected calendar table to do a flexible headcount analysis, but I didn't create a calendar table in this example. 

I attach an example pbix file.  

[O-K · Accepted solution]

Firstly thank you for the quick response.
I somehow keep getting an error in the second half

[Khushidesai0109 · Accepted solution]

Hiii Try this DAX
Adjusted Department StartDate =
VAR Departmentstartdate =
CALCULATE(
MAX('EU HR'[Adjusted End/present day]),
FILTER(
'EU HR',
'EU HR'[Adjusted End/present day] < EARLIER('EU HR'[Adjusted End/present day])
)
)
RETURN
IF(
ISBLANK(Departmentstartdate),
'EU HR'[Adjusted End/present day],
Departmentstartdate
)

If this solution helped you please give a thumbs up and accept this reply as a solution Thank You!! Regards!!

[Khushidesai0109]

Hiiii

1. Adjusting Start Dates:

   • Sort the data by Employee ID and Start Date in descending order.
   • Add an index column to keep track of the order of entries for each employee.
   • Use a custom column to calculate the previous end date for each row using the index column.
   • Merge this calculated end date back to the original table based on the Employee ID and index columns.
   • Replace the original Start Date column with the calculated end date.
   • Add an index column by clicking on "Add Column" > "Index Column" > "From 1."
   • Click on "Add Column" > "Custom Column" and enter the following formula to calculate the previous end date:
     = if [Index] = 1 then null else Table.Column(PreviousRow, "End date")
   • Replace "PreviousRow" with the name of your previous row if it's different.

   • Keeping Only One Active Status Per Employee:

     • Sort the data again by Employee ID and Start Date in descending order.
     • Add a custom column to assign ranks:
     • Filter the data to keep only rows where the rank is 1:
       • Click on the dropdown arrow next to the custom rank column.
       • = if [Status] = "Active" and [Index] = 1 then 1 else null
       • Choose "Number Filters" > "Equals" > "1.
         
         If this solution helped you please give a thumbs up and accept this reply as a solution

[O-K]

Thank you for taking the time to reply.

Some IDs are have more than 3 or more entries and some just mentioned once. That's  probably why the Previous Row isn't working.

[Khushidesai0109]

Adjusting Start Dates:

• Open Power BI and go to the "Home" tab.
• Click on "Transform Data" to open Power Query Editor.
• In Power Query Editor, locate your table.
• Click on the "Transform" tab and then "Sort Rows."
• Choose Employee ID and Start Date, both in descending order.
• Add an index column by clicking on "Add Column" > "Index Column" > "From 1."
• Group by Employee ID and create a custom column to calculate the previous end date
  = List.Max(List.RemoveNulls(Table.Column(PreviousRow, "End date")))
• • Merge the calculated end date back to the original table:
    • Click on "Home" > "Merge Queries" > "Merge Queries as New."
    • Choose your original table as the second table and Employee ID as the key for both tables.
    • Expand the merged column to include the calculated end date.
  • Replace the original Start Date column with the calculated end date:
    • Right-click on the calculated end date column and select "Replace Values" > "Replace Errors" with null.
    • Rename the calculated end date column to "Adjusted Start Date."
    • Right-click on the original Start Date column and select "Remove."

• Keeping Only One Active Status Per Employee:

  • Sort the data again by Employee ID and Start Date in descending order.
  • Add a custom column to assign ranks
    = if [Status] = "Active" and [Index] = 1 then 1 else null
  • Filter the data to keep only rows where the rank is 1:
    • Click on the dropdown arrow next to the custom rank column.
    • Choose "Number Filters" > "Equals" > "1."
    • Click OK.
      
      
      
      If this solution helped you please give a thumbs up and accept this reply as a solution
      Thank You!! Regards!!