Average Tenure by Department / Location by Time

[sidexcel]

Hi All,

I am posting this again as I did not have too much luck getting it correctly answered previous time. I did however make some progress in getting the correct result  by employee (even if not at the aggregation level that I am looking for). 

The Excel Data set is here.

The PBIX file is here. 

Essentially I am looking to create a report that shows average tenure calculated over time. The measure that I created seems to work perfectly if I look at by employee but not if I would like to aggregate by a dimension (such as department or location). 

My measure is as follows:

Tenure = 
VAR HireDate = CALCULATE(MAX('Employee Roster'[Hire Date]), ALLEXCEPT('Employee Roster', 'Employee Roster'[External ID]))
VAR TermDate = CALCULATE(MAX('Employee Roster'[Term Date]), ALLEXCEPT('Employee Roster', 'Employee Roster'[External ID]))
VAR EOM = MAX('Calendar Table'[Date])
VAR StartDate = IF(HireDate<=EOM, HireDate, blank())
VAR EndDate = IF(TermDate=BLANK(), EOM, IF(EOMONTH(TermDate,0)>=EOM, min(EOM,TermDate), BLANK()))
VAR TimeElapsed = calculate(averagex(all('Employee Roster'),DATEDIFF(StartDate, EndDate, Month)))
RETURN
TimeElapsed

The solution is incorrect because the aggregation for average is not working as seem in the screenshot:

Thank you in advance! 

@amitchandak you were very helpful in the previous post! 

Edit: Updated correct link! 

[TheoC]

Hi @sidexcel 

It looks like both links are to Excel files?  Could you just check that the PBIX is uploaded correctly?

Thanks heaps,

Theo

[sidexcel]

Hi @TheoC sorry about that! I fixed the links in the original post! 

[v-rzhou-msft · Accepted solution]

Hi @sidexcel ,

Here I create a calculated table to achieve your goal.

Table = 
VAR _GENERATE =
    GENERATE (
        SUMMARIZE (
            'Employee Roster',
            'Employee Roster'[Location],
            'Employee Roster'[External ID]
        ),
        SUMMARIZE('Calendar Table','Calendar Table'[Month & Year],'Calendar Table'[MonthnYear])
    )
VAR _ADD =
    ADDCOLUMNS (
        _GENERATE,
        "1",
            VAR HireDate =
                CALCULATE (
                    MAX ( 'Employee Roster'[Hire Date] ),
                    ALLEXCEPT ( 'Employee Roster', 'Employee Roster'[External ID] )
                )
            VAR TermDate =
                CALCULATE (
                    MAX ( 'Employee Roster'[Term Date] ),
                    ALLEXCEPT ( 'Employee Roster', 'Employee Roster'[External ID] )
                )
            VAR EOM =
                CALCULATE ( MAX ( 'Calendar Table'[Date] ) )
            VAR StartDate =
                IF ( HireDate <= EOM, HireDate, BLANK () )
            VAR EndDate =
                IF (
                    TermDate = BLANK (),
                    EOM,
                    IF ( EOMONTH ( TermDate, 0 ) >= EOM, MIN ( EOM, TermDate ), BLANK () )
                )
            VAR TimeElapsed =
                CALCULATE ( DATEDIFF ( StartDate, EndDate, MONTH ) )
            RETURN
                TimeElapsed
    )
RETURN
    _ADD

Then create a visual by columns in this table directly and use average function in matrix value field.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sidexcel]

Hi @v-rzhou-msft 

This is great! I am curious, is it possible to do this completely via virtual table and not creating a helper table to achieve?

Highly grateful of your help!

-Sid