Forum Discussion
Duplicate Data entries
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!!
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.