topic Re: DAX Calculate age between two dates on two different tables in DAX Commands and Tips

DAX Calculate age between two dates on two different tables

Chockers1 — Thu, 06 Apr 2023 02:48:19 GMT

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

Re: DAX Calculate age between two dates on two different tables

tamerj1 — Thu, 06 Apr 2023 03:00:30 GMT

Hi Chockers1

please try

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

Re: DAX Calculate age between two dates on two different tables

Chockers1 — Thu, 06 Apr 2023 03:19:23 GMT

Thanks mate

The error message based on that is below

Sounds like the relationship isn't working?

Re: DAX Calculate age between two dates on two different tables

tamerj1 — Thu, 06 Apr 2023 03:28:56 GMT

Chockers1
Please try

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

Re: DAX Calculate age between two dates on two different tables

Chockers1 — Thu, 06 Apr 2023 03:56:34 GMT

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

Re: DAX Calculate age between two dates on two different tables

tamerj1 — Thu, 06 Apr 2023 05:03:36 GMT

Chockers1

Please present one example

Re: DAX Calculate age between two dates on two different tables

Chockers1 — Thu, 06 Apr 2023 18:15:55 GMT

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

Re: DAX Calculate age between two dates on two different tables

tamerj1 — Thu, 06 Apr 2023 18:29:19 GMT

Chockers1

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

Re: DAX Calculate age between two dates on two different tables

Chockers1 — Thu, 06 Apr 2023 20:26:08 GMT

Awesome mate, that has got it working!

The only thing left it shows as a minus age

Any ideas?

Re: DAX Calculate age between two dates on two different tables

tamerj1 — Thu, 06 Apr 2023 20:45:00 GMT

Chockers1

Oh! Apologies for that

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

Re: DAX Calculate age between two dates on two different tables

Chockers1 — Thu, 06 Apr 2023 22:13:37 GMT

thank you so much works beautfully!!!