Return an employee's historical group based on their change date and one filter per month

[Anonymous]

Hello community!😃 I need help with a filter problem

Context: I am setting up an absenteeism dashboard, and the problem I have is that the system that records absences does not respect the employee's historical labor data. Therefore, when I want to take action, they don't give me the right results for a past moment.

Example:

The employee whose file is 2534 before 1/7/2022 was an analyst, therefore his "Group" was "T". From 1/7/2022 it became a sector leader, therefore its "Group" became "M".
But as you can see in the photo, if I filter month 6, that person already appears to me as group M when I was not yet. Therefore, when doing the numerical controls of absent and theoretical hs by group gives me an incorrect result

So, what I need is to ensure that when a person has moved from one group to another (it implies that he changed the category of his position), I can select a particular month in the segmenter and return the group to which he belonged at that time.

• As general data I have:
1. Calendar table

2. Other tables with the theoretical hours that the collaborator should fulfill, and the hours of absence that he had
3. List of personnel: from here I get the files and names of the people mainly, and it is the table that does not keep the historical group at a given time of the staff.

4. Trajectory facts:

From here, the main columns are:
- File("Legajo") :which relates to the personnel table
-The date of movement("FechaMovimiento"): which is the date on which the person changes groups
- "Cond.Grupo": It is the deduction of the group that the person occupied for each date of movement. Since the absenteeism system does not save the group history, I deduced it with data from the personnel system with the following calculated column:

Cond.Grupo = SWITCH(True(),

[Empresa]="0" && [Categoria_Puesto]="JORNALIZADOS","J",

[Empresa]="0" && [Categoria_Puesto]="JEFE","M",

[Empresa]="0" && [Categoria_Puesto]="PASANTES","U",

[Empresa]="0" && [Categoria_Puesto]="ESPECIALISTA","T",

[Empresa]="0" && [Categoria_Puesto]="ANALISTA","T",

[Empresa]="0" && [Categoria_Puesto]="ADMINISTRATIVO","T",

[Empresa]="1" && [Categoria_Puesto]="ESPECIALISTA","V",

[Empresa]="1" && [Categoria_Puesto]="ANALISTA","V",

[Empresa]="1" && [Categoria_Puesto]="ADMINISTRATIVO","V")

I leave a link so you can ecceder to excel with complete tables:

https://docs.google.com/spreadsheets/d/1ZF8bzALOX6f9YGvaor767RihipaNkDPf/edit?usp=sharing&ouid=11142...

I also show you how I have related tables:

--------------------------------------------------EXPECTED RESULT---------------------------------------------------------

So what I need is that taking as reference the "Date of movement", when filtering in a particular month, each person has their historical group.
In the case of the photo below, the person had a group change on 1/7/2022, so if I filter month 6, I should get out that their group is T. And if you filter month 7 onwards, you should tell me that your group is M

This would be the simplest case, it could happen that a person has changed groups more than one 2 times. As for example the collaborator of file 2279 (in case you want to test the operation of your logic with that example too)

Of course, thank you very much for your help🤗

[Anonymous]

Hi @Anonymous 

Given the complexity of your tables and relationships, could you provide your pbix file(remove sensitive info) for reference?

Best Regards,
Community Support Team _ Ailsa Tao

[Anonymous]

Hi @Ailsla-msft! Thank you for you answer

Of course, I attach the link to download the file. Thank you!

https://drive.google.com/file/d/1K6Fhb-mw0kWzZqv08Yhsxr5wWED3IE9N/view?usp=sharing 

Best regards