Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 kn...
Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    7 years ago

    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's avatar
    v-eachen-msft
    7 years ago

    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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
v-eachen-msft
Community Support
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      v-eachen-msft
      Community Support
      7 years ago

      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.