Historical average age dax

[BI-1294]

Hi all, 

I'm struggling to calculate the average historical age of the staff that was active in a certain moment. Let's say I want to see for every past month which was the staff age average of the active staff (for active staff I mean with a contract that was not ended).

This is the simplified table that I have (currently I have also 2 inactive relationships between the calendar date and end_date and start_date):

StaffID (key of the table - no duplicates) 

Start_Date (hiring date) 

End_Date (last day of work) 

Birth_Date

[Greg_Deckler · Accepted solution]

@BI-1294 Seems like:

Measure =
  VAR __Date = MIN('Calendar'[Date]) //or max
  VAR __Table = 
    ADDCOLUMNS(
      'Table',
      "__AgeInDays" = (__Date - [Birth_Date]) * 1.
    )
RETURN
  AVERAGEX(__Table,[__AgeInDays])