Average experience by employee in DAX

[Draszor]

Hi All, 

I have a simple (virtual) table in my data model, showing 3 columns:

employee number // employment date // date of dismissal

I have as wll another (virtual) table with callendar.

I am trying to build a measure showing average number of days the employees stayed with us at a given date (year and month ends). this equals to total sum of days between employment and given date for all employees, dividing by total number of employees. 

I built the measure but it does not work as expected. the nominator (total number of days active employees worked at given date) shows correct igures per employee, but total for year looks weared. for instance - I ger 8220 ttoal for Dec 2013, while there are over 200 employees, many of them working over 8000 days for the company. what do I mess in the below measure to assure total per date is correct?

my measure:

Average Experience =

var maxDate =

max(CALLENDAR[Date]

)

var daysbetween =

calculate(

datediff(

min(

'Experience Tab'[EMPLOYED]

)

,

maxDate,DAY

),

'Experience Tab'[EMPLOYED]<maxDate,

('Experience Tab'[DISMISSED] > maxDate || ISBLANK('Experience Tab'[DISMISSED])

)

)

return

daysbetween

[amitchandak]

@Draszor , Try a measure like

divide(sumx(Table, datediff(Table[employment date], coalesce(Table[date of dismissal], today()), day)), count(Table[employee number]))

This is Avg in Days 

[Draszor]

thank you amitchandak,

I used your idea but had to modify it, as my goal was to plot the average experience into the time line, showing what is the average working period in the company, per year. if most experienced people leave, the average drops. if new-comers leave, average increases. in both cases the turnover ratio could be the same. 

first, I had to redo my data model, resigning from my virtual table that was showing emloyee / date of employment / date of release - REASON - using this table in the nominator of DIVIDE(), it was giving the correct results for the total company, but I was not able to filter by any aggregate (unfortunatelly I work on one table of HC_DATA, not on the fact table and the lookup table),

my working formula for the nominator of my DIVIDE() in short is:

Var ExperienceDays = 
calculate(
    sumx(
        HC_DATA,
        DATEDIFF(HC_DATA[employed at],
        COALESCE(maxDate,
        HC_DATA[released at]
        ),
        DAY
        )
    ),
    ALLEXCEPT(HC_DATA,
    HC_DATA[Emp no SAP],HC_DATA[AGGR_0],HC_DATA[Name],HC_DATA[Surname],HC_DATA[Aggr_1],HC_DATA[Aggr_2],HC_DATA[Org_Unit_1],HC_DATA[Org_Unit_2],HC_DATA[Org_Unit_3],HC_DATA[Wydział]
    ),
   
    HC_DATA[Employed at] <= maxDate,
    (HC_DATA[Released at]>maxDate || ISBLANK(HC_DATA[Released at])
    )
)

one weird issue I observe is the must to keep the ALLEXCEPT() inside the formula. without it, my total days are much lower than they should.