How to find correct working start date of employee

[obuolys123]

Hello, 

I have this situation. I need to find the year when employee started working first, but i have difficulties when there is different situations. 

Tom started working as a CarDriver on 2021-01-01 and is still working (0000-00-00 means that he is still working). Then he started also working as a TruckDriver in the same company, but left after 1 month. System in this case shows that he has 2 dates when he started working, but the fact is that he was still a member of a company, just had different jobs. I need to get only the first date. It would be simple to use FIRSTDATE, but the issue is that Ron has the same situation, only he has 2 start dates and i need both of them, because he left from the company for some time.

Has anyone any ideas how to get first date in some cases and two dates in case of Ron. I attached photo of example bellow. Data is very similar.

Thank you.

[v-chenwuz-msft · Accepted solution]

Hi @obuolys123 ,

I think your model is not right. For example, if Ron come back but his second job is TruckDriver, how would you identify if he was "come back" or "change to another job" ?

The workID should change if the employee quit the company.

In your case, i create a column named [if quit]:

if quit =
VAR _first =
    CALCULATE (
        MIN ( 'Table'[startdate] ),
        FILTER ( 'Table', [workID] = EARLIER ( 'Table'[workID] ) )
    )
VAR _firstend =
    CALCULATE (
        MIN ( 'Table'[enddate] ),
        FILTER (
            'Table',
            [workID] = EARLIER ( 'Table'[workID] )
                && [startdate] = _first
        )
    )
RETURN
    IF (
        _firstend = DATE ( 9999, 12, 31 )
            || _firstend >= [startdate],
        _first,
        [startdate]
    )

Then, create a new table via summarize.

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[obuolys123]

Hello, @v-chenwuz-msft 

Yes, and it's a huge problem for the IT, that company saves data about workers with the same workerID. Don't know why they do that, but not much could be done... They do have some indicators to know if Ron "came back" or "changed to another job", but I dont have them at the moment.

Speaking about your model, it works perfectly, thank you very much!

I also wanted to ask you for help one more time for an update on DAX you showed. I found another case. Let say Kevin worked in the company as a truckdriver for a while, he left. Later he started woking as a cardriver, never left and after couple months he also started working as a truckdriver. In your model I get 3 different dates. What I need is 2 dates, when he first came to work and his second date, because he never left. I attached foto bellow. Yellow is the case and what I get by using your model, green is what I need. So maybe you have some kind of update to what you offered earlier?

[obuolys123]

@v-chenwuz-msft Found a way, nevermind.

Thank you for the guidance.

[PC2790]

You can have  a new table with the required information.

Here you go:

Joining Dates Table = SUMMARIZE(MyTable,MyTable[Name],MyTable[Worker],"Joining Date",min(MyTable[StartDate]))

And you will get something like this:

Original data:

[obuolys123]

Hi, @PC2790 

Thanks for the help, but as I noted before, in Ron case I need two dates, because there was a short period when he was not working in the company. E.g. I need to count how many workers joined company yearly, so I would need to include him in 2021 and 2022. Tom in this case has only one start date, because he never left the company, only added some extra job (Truck driver for some period), so I need to return only one date. In your example there are different dates on Tom. Do you have any extra ideas how to get through it?

[amitchandak]

@obuolys123 , Based on what I got

new column

New Start Date = minx(filter(Table, [WorkerID] =earlier([WorkerID]) ), [Start Date])

[obuolys123]

Hi, @amitchandak 

Thanks for the help, but in this case I get always the first date, as I said, if Ron quits from the job and e.g. decides to come back after a year, I need both of his dates. So i think in this case there is a need to use end date somehow. What do you think?