Forum Discussion
Analyse Employee Data
Hi,
I need some help with my employee data. My table looks like this:
| name | entry | exit |
| A | 01.05.2011 | 01.06.2013 |
| B | 01.07.2013 | 01.07.2015 |
| C | 01.01.2017 | null |
I want to know, how long (relative to a full year) each employee worked for each year. The result table should look like this:
| name | year | amount |
| A | 2011 | 0,66666667 |
| A | 2012 | 1 |
| A | 2013 | 0,41666667 |
| B | 2013 | 0,5 |
| B | 2014 | 1 |
| B | 2015 | 0,5 |
| C | 2017 | 1 |
| C | 2018 | 1 |
| C | 2019 | 1 |
The current year (here: 2019) should always count 1 unless there is an exit date (if the exit date for C was 01.07.2019, the value for C - 2019 would be 0,5).
I have no idea how to do it - can anyone help?
Thanks so much!
Mark
Hi Anonymous ,
First, you need a new table to save all dates.
Year = GENERATESERIES ( YEAR ( MIN ( 'Table'[entry] ) ), YEAR ( NOW () ), 1 )
Then cross join two tables to get a new temporary table.
TEMPTABLE = CROSSJOIN ( 'Table', 'Year' )
Create the final table after filtering.
FinalTable = CALCULATETABLE ( 'TEMPTABLE', FILTER ( 'TEMPTABLE', 'TEMPTABLE'[Value] <= IF ( ISBLANK ( YEAR ( 'TEMPTABLE'[exit] ) ), 2019, YEAR ( 'TEMPTABLE'[exit] ) ) && 'TEMPTABLE'[Value] >= YEAR ( 'TEMPTABLE'[entry] ) ) )Now you can create a column to get your result with your logic.
Best Regards,
Eads
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
The 2019 is the year at which the date ends. I use this hard code to get test result faster. So Year(today()) can work. As for YEAR(LASTDATE('Year'[End])), it is the same as your code to get the year at which the date ends.
Best Regards,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi Anonymous ,
First, you need a new table to save all dates.
Year = GENERATESERIES ( YEAR ( MIN ( 'Table'[entry] ) ), YEAR ( NOW () ), 1 )
Then cross join two tables to get a new temporary table.
TEMPTABLE = CROSSJOIN ( 'Table', 'Year' )
Create the final table after filtering.
FinalTable = CALCULATETABLE ( 'TEMPTABLE', FILTER ( 'TEMPTABLE', 'TEMPTABLE'[Value] <= IF ( ISBLANK ( YEAR ( 'TEMPTABLE'[exit] ) ), 2019, YEAR ( 'TEMPTABLE'[exit] ) ) && 'TEMPTABLE'[Value] >= YEAR ( 'TEMPTABLE'[entry] ) ) )Now you can create a column to get your result with your logic.
Best Regards,
Eads
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi v-eachen-msft ,
thank you! I think that will be the solution!
One question:
You have hard-coded "2019" as the current year. When we go to the next year, this would not be valid any more, right? Can we code that differently?
Thanks!
Mark
Hi Anonymous ,
You can use this code:YEAR(LASTDATE('Year'[End])) to replace 2019.Best Regards,
Eads
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.