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

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

Reply
DAXEupheme
New Member

Employee Tenure using Power Query or DAX

I am needing help with the following.

* Ideally done via Power Query M, but DAX measure would work as well.

* I have a separate dimDATES table, and it is a normal 'calendar' table, not 4-4-5, etc. -- so the built in TimeIntelligence functions can be used.

 

ISSUE - my FACT table contains employees who appear in the report for each month every time they take a call, which means they can appear multiple times each month.  I'm using a PivotTable with Months and EmployeeName to show (by number) the unique times they are appearing each month, as a running total.

 

For example, if John Smith takes a call in NOV 2018 (and it was their 1st month taking a call) then the TENURE column in the PivotTable will show '1'.  If they take a call again the next month, then it will show '2', etc, and it will show a '3' if they are take a call in JAN 2019, etc etc.  So a running total.  All of my measures thus far are counting the total number of times they take calls each month and adding them up, which is not what I want.

 

Ideally, I would like this to be done in POWER QUERY using M -- create a new column TENURE that recognizes the first month an Agent takes a call (thus their first month of employment) and then recognize if they take a call during the next month, the tenure for that month becomes '2', etc. etc.

 

Much thanks!

 

Here are the DATE COLUMN names I have thus far in my QUERY -

PowerQuery-EmpAndDates.png

1 REPLY 1
Anonymous
Not applicable

any chance you can upload some sample data from your fact table?

Helpful resources

Announcements
November Power BI Update Carousel

Power BI Monthly Update - November 2025

Check out the November 2025 Power BI update to learn about new features.

Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Solution Authors
Top Kudoed Authors