Calculated Measure and Averages

Hi Anonymous ,

At first, you need to create a calendar table.

Calendar =
CALENDAR ( MIN ( 'Table'[Date of first Contract] ), TODAY () )

Then add three new columns in it.

Year =
YEAR ( 'Calendar'[Date] )

StartDate =
DATE ( 'Calendar'[Year], "01", "01" )

EndDate =
IF (
    'Calendar'[Year] = MAX ( 'Calendar'[Year] ),
    MAX ( 'Calendar'[Date] ),
    DATE ( 'Calendar'[Year], "12", "31" )
)

Now create two new measures.

CountPerYearPerEmployee =
VAR selectDateS =
    SELECTEDVALUE ( 'Calendar'[StartDate] )
VAR selectDateE =
    SELECTEDVALUE ( 'Calendar'[EndDate] )
VAR selectYear =
    SELECTEDVALUE ( 'Calendar'[Year] )
VAR cease =
    SELECTEDVALUE ( 'Table'[Date of cease] )
VAR first =
    SELECTEDVALUE ( 'Table'[Date of first Contract] )
VAR se =
    IF (
        YEAR ( first ) > selectYear,
        0,
        IF (
            YEAR ( first ) < selectYear,
            selectDateS,
            IF ( YEAR ( first ) = selectYear, first )
        )
    )
VAR de =
    IF (
        se = 0,
        0,
        IF (
            cease = BLANK ()
                && se <> 0,
            selectDateE,
            IF (
                selectYear > YEAR ( cease ),
                0,
                IF (
                    selectYear < YEAR ( cease )
                        && cease <> BLANK (),
                    selectDateE,
                    IF ( selectYear = YEAR ( cease ), cease )
                )
            )
        )
    )
RETURN
    ROUND (
        IF ( de = 0, 0, DATEDIFF ( se, de, DAY ) )
            / DATEDIFF ( selectDateS, selectDateE, DAY ),
        2
    )

CountPerYear =
SUMX ( VALUES ( 'Table'[Employee Code] ), [CountPerYearPerEmployee] )

Create visuals to show your result. You can use the CountPerYear to calculate the average.

Best Regards,

Eads

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