Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Steph83
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

 

Thnaks in advance for your support

Stephane

 

1 ACCEPTED SOLUTION
johnt75
Super User
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.

View solution in original post

5 REPLIES 5
johnt75
Super User
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.

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?

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 ?

Hi john,

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

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors