Average age of customers by year

[alya1]

Hi All,

I have data tables like below:

All customer data including birthday and many other traits

ID	Birthday	other columns...
111	1/1/1995
222	2/2/2000
333	3/3/1890
...	...

Historical customer list table showing the historical date and any ID that was an active customer on that date

ID	Upload Date
111	1/1/2020
222	1/1/2020
111	1/2/2020
222	1/2/2020
333	1/2/2020
...	... till today

Calendar table with all dates from 1/1/2020 to today.

Relationships:

Calendar table is linked to historical table by upload date.

All customer data table is linked to historical table by ID. 

Is there a way to show average age of our active customers by year? Thank you!

[gmsamborn · Accepted solution]

Hi @alya1 

I made up some random data and was wondering if these measures would help.

Age = 
    IF(
        MAX( 'Customer'[BirthDate] ),
        SELECTEDVALUE( 'Date'[Year] ) - YEAR( MAX( 'Customer'[BirthDate] ) )
    )


Avg Age = 
    AVERAGEX(
        ALLSELECTED( 'Historical'[ID] ),
        [Age]
    )

Let me know if you have any questions.

Avg Age.pbix

[alya1]

Hi gmsamborn, thank you very much for your reply!
I tried following along with your DAX but sadly it didn't work. I think it's because my Calendar table only have dates "from 1/1/2020 to today." So every customers age for year 2021 is 18 and 2022 is 19 and so on. Do you have any other ideas? 

[gmsamborn]

Hi @alya1 

Is the realtionship between Historical and Customer set to both directions?

If that isn't the problem, can you please supply a pbix file with data that covers your problem as well as expected results from THAT data.

[alya1]

I think that was it! Thank you so much 😄 

[gmsamborn]

@alya1 

I'm glad I could help.