Average lenght of employment with multiple contracts

[frankhofmans]

hi all,

i want to calculate the moving average length of employment in weeks. I created the following measure:

Average lengt employment = CALCULATE(averageX('Employments',

        VAR date1 = MAX( 'Employments'[EMPLOYMENT_START] , MIN('Employments'[EMPLOYMENT_START]) )

        VAR date2 = IF('Employments'[EMPLOYMENT_END]= BLANK(),

            MAX('Date table'[Date]),

            MIN( 'Employments'[EMPLOYMENT_END], MAX('Date table'[Date]))

        )

        VAR weeks = DATEDIFF(date1, date2, WEEK)

       RETURN weeks))

This formula works, but i have a lot of employee who start with a first contract (mostly a month), followed by a contract for a year or a permanent contract. With this formula the average of both separate contracts is taken, but i want the total average length (so the length of both contracts).

Employee ID	Start	End
001	1 jan 2021	31 jan 2021
001	1 feb 2021	31 jan 2022
002	1 mar 2021
003	1 mar 2021	31 mar 2021
003	1 apr 2021	1 jun 2022
Etc

Does anyone know ho i can get this result?

Thanks in advance,

Regards, Frank

[Fowmy]

@frankhofmans 

Create the following measure and give it try:

Avg Weeks = 
VAR __WeeksTable =
    ADDCOLUMNS (
        ADDCOLUMNS (
            VALUES ( Employments[Employee ID] ),
            "@StartDate", CALCULATE ( MIN ( Employments[Start] ) ),
            "@EndDate", COALESCE ( CALCULATE ( MAX ( Employments[End] ) ), MAX('Date table'[Date]) )
        ),
        "Weeks", DATEDIFF ( [@StartDate], [@EndDate], WEEK )
    )
RETURN
    CALCULATE ( AVERAGEX ( __WeeksTable, [Weeks] ) )

[frankhofmans]

hi Fowmy,

Thanks for your reply. I can't duplicate the formula. 2 times addcolumns? and the "@" parts in the formula are also VAR's? If i copy the formula, i can't select "Weektabel" after the calculate(averagex....

Thanks in advance!

Regards,

Frank

[Fowmy]

@frankhofmans 

Please an example data in a PBIX file, you can save the file in Google Drive, One Drive etc and share the link here.

[frankhofmans]

hi, this is the link,

https://drive.google.com/file/d/1FBm65fcRqLRTEl3WjCG0LdiGRt9Bj-u9/view?usp=sharing

thanks!

[Fowmy · Accepted solution]

@frankhofmans 

Please check the attched file