How to calculate average age of a group of persons in XX years?

[Steph83]

I have a group of 300 people ("Artists") for whom I have their date of birth ("date of birth").

I would like to know the average age of this group on a specific date that I would select in a dashboard filter.

In fact my data base gathered all persons, but all are not available all the time. So I need to know, among the persons who are there, at a specific date, the average age of the persons

Thnaks in advance for your support

Stephane

[johnt75 · Accepted solution]

You could create a measure like

Average age =
VAR ReferenceDate =
    SELECTEDVALUE ( 'Slicer date'[Date] )
VAR Result =
    AVERAGEX ( Artists, DATEDIFF ( Artists[Date of birth], ReferenceDate, YEAR ) )
RETURN
    Result

where 'Slicer date'[Date] is the column you are using on the slicer.

[Steph83]

Good day,

Something is missing. I have well the average of the list of persons at a selected date however, it gives the average of all the persons whatever the filter applied on the page (gender, above 30....). How can I include the selected filters in the formula so as to calculate the average on the selected persons only?

[johnt75]

The code I posted should respect any filters placed on the Artists table. If it is not doing that can you post a link to a sample PBIX ?

[Steph83]

Hi john,

Indeed, I was using two tables. Now it has been solved. Thanks

[Steph83]

Hello John, 

When I read your proposal, it seems so easy, but I wouldn't have been able to find it!!!

That's work perfectly, thank you