Employee Tenure using Power Query or DAX

Posts: 1
Joined: Thu May 23, 2019 2:03 am

Employee Tenure using Power Query or DAX

Postby DistinctDAX » Thu May 23, 2019 2:17 am

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 important columns in POWER QUERY for my FACT TABLE. Of course, all of these 'date' columns are not needed in this FACT table, since I have a related DATE table (again, normal calendar table), but I left them here since they may help with a M formula to solve this very problem. Also note that these 'date columns' exist as well in the dimDATE table.


Posts: 1053
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Employee Tenure using Power Query or DAX

Postby MattAllington » Fri Jun 14, 2019 3:24 pm

If you post a realistic sample workbook loaded up in Power Pivot showing what you are after, I will take a look. Just PM me if you post it.
Matt Allington is Self Service BI Consultant, Trainer and Author of the Book "Supercharge Power BI".
Power BI Training

Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 1 guest