Forum Discussion
Helper ISomething more practical than an IF Statement
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
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.
5 Replies
J_o_n_a_sYeah 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.
v-yingjlCommunity Support
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
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.
