Analyse Employee Data

[Anonymous]

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

[v-eachen-msft · Accepted solution]

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.

[v-eachen-msft · Accepted solution]

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.

[Anonymous]

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

[v-eachen-msft]

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.

[Anonymous]

Hi @v-eachen-msft ,

I am not sure if that works as it should.

I now used Year(today()) to replace 2019, because I always want to make the calculation until the current date. 

Can you please tell me again what the "2019" exactly does within the code?

Thanks!

Mark

[v-eachen-msft · Accepted solution]

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.

[Anonymous]

I want to explain again, what the goal here is:

In the end there should be a chart which shows how many employees there are working for each year. 

And from my point of view there is now way to archive this with the first table, right?

Maybe that helps!