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 -

    Tuesday, May 21, 2019 7:11 PM

All replies

  • Excel 2010 to 2019 Power Query (aka Get & Transform)
    Accumulate count of active months in 2018 for ten agents.
    No DAX, no Date Table needed or used.

    Tuesday, May 21, 2019 11:04 PM
  • Herbert,

    Thanks for your help, but this won't work for what I need to do. This needs to be accomplished via POWER BI DAX as a measure, OR (ideally) using POWER QUERY as a new column.

    I need to create other measures off of this, etc..


    Wednesday, May 22, 2019 3:23 PM
  • You can try adding the following steps:

        groupedRows = Table.Group(
              (i) =>
                      (j) => List.PositionOf(List.Distinct(i[YrMon]), j[YrMon]) + 1
              type table
        combinedTables = Table.Combine(groupedRows[Table])

    Wednesday, May 22, 2019 4:58 PM
  • Could you provide me with the exact code that you inserted, and where in your code it was inserted? Without the source data, I cannot test anything. 
    Wednesday, May 22, 2019 6:54 PM
  • Hmmm. The link takes me to an Excel file that is too large to open in Excel Online. Unfortunately, using the open in Excel option loads nothing. There is no indication that the file is csv.
    Wednesday, May 22, 2019 10:19 PM