Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
AgosP99
Frequent Visitor

Return an employee's historical position based on their change date

Hello community😃

 

I need your help to get the job that a person had in each month

This is the base table:

EmployeeChangeDateGroup
John1/1/2022A
Daniel1/1/2022A
Emi31/5/2019B
Emi1/7/2022C
Carl14/4/2020A
Carl1/5/2021B
Carl1/5/2022C

 

Each employee has the date on which he or she joined a new job and its corresponding job or group code. For example: Emi belonged to group B from 5/31/2019 to 6/30/2022. From 1/7/2022 to the present, it belongs to group C

 

So what I need is that for each month selected in the segmenter, power bi returns only the group of each employee for that month instead of all their history.

Examples of expected results:

Option 1

SelectedMonth&YearJune 2022 
   
EmployeeChangeDateGroup
John1/1/2022A
Daniel1/1/2022A
Emi31/5/2019B
Carl1/5/2021B
   
   
FilterMaxDate30/6/2022 
FilterMinDate1/6/2022 

 

Option 2

SelectedMonth&YearJuly 2022 
   
EmployeeChangeDateGroup
John1/1/2022A
Daniel1/1/2022A
Emi1/7/2022C
Carl1/5/2022C
   
   
FilterMaxDate31/7/2022 
FilterMinDate1/7/2022 

 

Option 3:

SelectedMonth&YearAugust 2022 
   
EmployeeChangeDateGroup
John1/1/2022A
Daniel1/1/2022A
Emi1/7/2022C
Carl1/5/2022C
   
   
FilterMaxDate31/8/2022 
FilterMinDate1/8/2022 

 

 

Thank you very much for your help!

2 REPLIES 2
tamerj1
Super User
Super User

Hi @AgosP99 

is the month slicer coming from a date table? If so, is there any relationship brewing the two tables?

Hi @tamerj1 !

Yes, the month segmenter comes from a date table. There is currently a one-to-many relationship between the dates table and the employee trajectory table (which is the one I exemplified at the beginning of the post)

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors