cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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

Stephane

1 ACCEPTED SOLUTION
Super User

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.

5 REPLIES 5
Super User

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.

Frequent Visitor

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?

Super User

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 ?

Frequent Visitor

Hi john,

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

Frequent Visitor

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