Analyse Employee Data

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.