Tracking Employees team that changes over time

DAX DAX Calculations
1

Hi,
I have a Following Situation
I have a dataset that needs reporting for a department that has several teams, and the Employees can be moved around different teams over a time.
Now I need to create a report on department level and employee level and show results on current and historical data,

  1. I need to show which team each employee was on a given date

  2. I also need to create a measure that shows me the team that employee is part of Today / Current Date

I referred to the following blog, its close but doesnt match the exact scenario

I have created a Card visual and a Table visual –
Table Visual – doesn’t give results for any employee or date selection
Card Visual shows the result but changes to show an error on some employee selections and in other cases if it’s showing a result for one selection of date range, selection of a different date range turns it to give an error

p1
p11258×522 15.3 KB

p2
p21186×510 16.6 KB

I had made several attempts on fixing Dax but didn’t succeed so have removed all the non-working measures and just left the one according to the Radacad Blog.
employees.pbix (114.7 KB)
Sample Output.xlsx (11.8 KB)

Would really appreciate any help in getting this correct

1 Like
2

Hi @jps,
Thank you very much for posting your query in the forum.
I have created the following measures that you can try to see if it is what you are looking for:

CurrentRole by Date =

VAR _CurrentDate = MAX( 'Calendar'[Date] )

VAR _Result = MAXX(

    TOPN(

        1,

        FILTER(

          'Employee Department Change',

          'Employee Department Change'[DateofHire] <= _CurrentDate

        ),

        [DateofHire],

        DESC

    ),

    [Team]

)

RETURN

_Result


CurrentRole Today =

VAR _CurrentDate = MAX( 'Calendar'[Date] )

VAR _Result = CALCULATE(

    [CurrentRole by Date],

    FILTER(

        ALL( 'Calendar' ),

      'Calendar'[Date] = TODAY()

    )

)

RETURN

IF(

    ISINSCOPE( 'Calendar'[Date] ),

    IF( _CurrentDate = TODAY(), _Result ),

    _Result

)

Regards

employees_JAFP.pbix (115.6 KB)

3

Hi @jps,
Have you been able to try the solution I shared?

Regards

4

Hey @jafernandezpuga
I am so sorry. I just realized, wrote a response, but didn’t submit it.

That worked for me Just perfect. - Thank you. Although I haven’t had a chance to implement on my project just yet as there was a slight change in the requirement and it kind of sidetracked but I am sure I have to come to it sometime soon.

Thank you for your support. And sorry again for leaving you with no response all this time.

5

Hi @jps ,
Don’t worry, I’m glad I could help you.

Regards,

1 Like