Forum Discussion
Duplicate Data entries
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)
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.
Firstly thank you for the quick response.
I somehow keep getting an error in the second halfHiii 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!!
10 Replies
Hiiii
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
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.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."
- Merge the calculated end date back to the original table:
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 solutionThank You!! Regards!!
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.
Firstly thank you for the quick response.
I somehow keep getting an error in the second halfHiii 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!!
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,
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!!