Something more practical than an IF Statement

[J_o_n_a_s]

Hi everyone, 

So i need to come up with a solution for when an employee changes department. The work hours of this employee from a certain date onwards have to be saved under the new department without affecting previous work hours. 

As a solution to this I came up with the idea of creating a new employee name (Jonas2) from the date that this employee changes department. In that case Jonas2 will be saved under department B and all his work hours will be saved under there too (while the old work hours of Jonas will remain under department A) . So this is what I came up with:

Is there a smoother way of doing this please, since this will get quite complicated once it needs to be done for say 10 employees. 

Thanks for your help

Jonas

[v-yingjl]

Hi @J_o_n_a_s ,

You need not to write if statements for each employees, just distinguish it by [Date], create column like this:

Column =
IF ( 'Table'[Date] > DATE ( 2020, 12, 31 ), [Employee] & "2", [Employee] )

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[J_o_n_a_s]

But what if different employees change department on different dates?

[v-yingjl · Accepted solution]

Hi @J_o_n_a_s ,

If different employees change department on different dates, I think it is necessary to compare date for each employee but you can try to optimize the formula, for example, using switch() function, using variables, like this:

Column =
VAR date1 =
    DATE ( 2020, 12, 31 )
VAR date2 =
    DATE ( 2020, 5, 8 )
VAR new = [Employee] & 2
RETURN
    SWITCH (
        [Employee],
        "Jonas", IF ( [Date] > date1, new, [Employee] ),
        "A",
            IF ( [Date] > date2, new & 2, [Employee] )
    )

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VijayP]

@J_o_n_a_s 

Tyring to understand the final objective .Should you create the data in a column only? What do you want to achieve as report , can you please share that as a snapshot?

[J_o_n_a_s]

Yeah preferably in a column.

Basically the same result as what I got but in a more practical way for when more employees change department on different dates. I need to do this for past data as well so there will be several department changes.