1st year Status

[FrancesHolland]

Hi to all,

I have some data about employees.

They can have several IDnumbers but I managed to have only one Name per employee.

A same employee can have several rows for the same period.

Some of them have the Status ZI for a few months and I want to add a column "1st year ZI" with the 1st year of the status ZI.

Idnummer	Name	Year	Period	Day1_period	Status	1st year ZI (FALSE)	1st year ZI (aim)
32505	Dupont	2022	12	1-12-2022
32505	Dupont	2023	1	1-1-2023
32505	Dupont	2023	2	1-2-2023
32505	Dupont	2023	3	1-3-2023
32505	Dupont	2023	4	1-4-2023
32505	Dupont	2023	5	1-5-2023
32505	Dupont	2023	6	1-6-2023	ZI	2023	2023
32505	Dupont	2023	7	1-7-2023	ZI	2023	2023
32505	Dupont	2023	8	1-8-2023	ZI	2023	2023
32505	Dupont	2023	9	1-9-2023	ZI	2023	2023
32505	Dupont	2023	10	1-10-2023	ZI	2023	2023
32505	Dupont	2023	11	1-11-2023	ZI	2023	2023
32505	Dupont	2023	12	1-12-2023	ZI	2023	2023
43202	Dupont	2024	1	1-1-2024	ZI	2024	2023
43202	Dupont	2024	1	1-1-2024	ZI	2024	2023
43202	Dupont	2024	2	1-2-2024	ZI	2024	2023
43202	Dupont	2024	3	1-3-2024

I need this column to be abble to create a chart : number of employees per first year status when not blank.

I've created a measure and it works in a table but not in a chart.

Measure: 

ZI_Year_In =

CALCULATE(

    YEAR(MIN(Table[Day1_period])),

    table[Status]<>""

)

I used this measure to create the column 1st year ZI but it I had a different 1st year ZI for each Idnummer.

Column

1st year ZI = [ZI_Year_In]

If you have some ideas to help me it would be very helpfull.

Thanks in advance

Frances

[xifeng_L]

Hi @FrancesHolland.

Not sure about the effect you want and the problem you are experiencing. But you could try the Row Label feature, which distinguishes between cases with the same name.

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[FrancesHolland · Accepted solution]

Hi @xifeng_L , thank you for your answer but I can't do that because the Idnummer is repeated (one or more per month). 

By the way I found a solution : I copy the table. I group it per Name and I choose minimum Date. Then I join table2 to the initial Table and I let appear the column minimum firstdate.

Regards,

Frances 

[v-hashadapu]

Hi @FrancesHolland , We are happy to hear that you have found the cause and resolved the issue. Thanks for sharing the details here.  Please mark your insights 'Accept as solution' to help others with similar problems find it easily. 
Thank you.

[lbendlin]

They can have several IDnumbers but I managed to have only one Name per employee.

Shouldn't that be the other way round? Name changes may occur but IDs should stay the same.

Please provide a more detailed explanation of what you are aiming to achieve. What have you tried and where are you stuck?

[FrancesHolland]

Hello @lbendlin ,

I totally agree, but the HR management tool has been changed and a new ID number has been assigned to each employee. The interesting thing is that sometimes the same employee has a new ID number when he changes function. So I created a register with an index and a unique name for each employee, because there were also name differences in the two HR systems. I could use the index, but in tables and graphs, I have to use the name, and it has to be unique for everything to be correct.

By the way I found a solution : I copy the table. I group it per Name and I choose minimum Date. Then I join table2 to the initial Table and I let appear the column minimum firstdate and do some transformation to have it not repeated.