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.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!!