DAX Calculate age between two dates on two different tables

[Chockers1]

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

[Chockers1]

Thanks mate

The error message based on that is below

Sounds like the relationship isn't working?

[tamerj1]

@Chockers1 
Please try

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

[Chockers1]

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

[tamerj1]

@Chockers1 

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

[Chockers1]

Awesome mate, that has got it working!

The only thing left it shows as a minus age

Any ideas?

[tamerj1 · Accepted solution]

@Chockers1 

Oh! Apologies for that

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

[Chockers1]

thank you so much works beautfully!!!

[Chockers1]

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

[tamerj1]

@Chockers1 

Please present one example 

[tamerj1]

Hi @Chockers1 

please try

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