Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Average Tenure by Department / Location by Time

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! 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    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.

4 Replies

  • TheoC's avatar
    TheoC
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Anonymous 

     

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

     

    Thanks heaps,

    Theo

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Anonymous ,

         

        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.