topic Re: How to calculate average age of a group of persons in XX years? in DAX Commands and Tips

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

Steph83 — Fri, 21 Oct 2022 12:46:39 GMT

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

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

johnt75 — Fri, 21 Oct 2022 13:04:40 GMT

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.

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

Steph83 — Fri, 21 Oct 2022 13:29:19 GMT

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

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

Steph83 — Sun, 23 Oct 2022 14:12:55 GMT

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?

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

johnt75 — Mon, 24 Oct 2022 08:13:38 GMT

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 ?

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

Steph83 — Mon, 24 Oct 2022 11:31:22 GMT

Hi john,

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