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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Chockers1
Frequent Visitor

DAX Calculate age between two dates on two different tables

Hi All,

 

I have two multiple tables on a cricket dashboard and want to work out the players age at the start of a match.

 

The first table is called "Bat" and this is what the dashboard is made up off. It has a column called start_date

 

The other table is called "PP" and that has the player profiles detials and importnatly a date called "Born". These are linked on the data table by a player ID.

 

I want to add to my dashboard using the daa from "Bat" an age calucalation essentially being - Born - Start Date and leaving just the age.

 

I've looked through heaps of forums and YT videos and cant quite work it out.

 

Really hoping someone can help.

 

Thanks

Rob

 

bat.png

1 ACCEPTED SOLUTION

@Chockers1 

Oh! Apologies for that

Average Age =
AVERAGEX (
Bat,
DATEDIFF ( MAXX ( RELATEDTABLE ( PP ), PP[Born] ), Bat[Start_Date], YEAR )
)

View solution in original post

10 REPLIES 10
Chockers1
Frequent Visitor

Thanks mate

 

The error message based on that is below

 

Sounds like the relationship isn't working?

 

error.pngrelationship.png

@Chockers1 
Please try

Average Age =
AVERAGEX (
    Bat,
    INT ( Bat[Start_Date] - MAXX ( RELATEDTABLE ( PP ), PP[Born] ) )
)

average age dax.pngaverage age.png

Thanks mate, this is what appears - is it possible to make that into how old a player is in just years?

@Chockers1 

Average Age =
AVERAGEX (
Bat,
DATEDIFF ( Bat[Start_Date], MAXX ( RELATEDTABLE ( PP ), PP[Born] ), YEAR )
)

Awesome mate, that has got it working!

 

The only thing left it shows as a minus age

 

Any ideas?age showing minus.png

@Chockers1 

Oh! Apologies for that

Average Age =
AVERAGEX (
Bat,
DATEDIFF ( MAXX ( RELATEDTABLE ( PP ), PP[Born] ), Bat[Start_Date], YEAR )
)

thank you so much works beautfully!!!

Thank you, thats the first time its got a calculation out 🙂

 

That gives me the average age, no quite what I was after.

 

I guess ideally i just want it to show the years difference between the start Date and the Born date

@Chockers1 

Please present one example 

tamerj1
Super User
Super User

Hi @Chockers1 

please try

Average Age =
AVERAGEX ( Bat, INT ( Bat[Start_Date] - RELATED ( PP[Born] ) ) )

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.