Calculating Customer Age at the time of Transaction

[homeshagarwal]

Hi all!

Need help with a DAX code to create a calculation to find out the Customer's Age at the date of the transaction and not as per TODAY. The following is my data model:

My Customer Table and Sales Table are connected based on Customer ID and not on a Date Field.

Could you please help me out here?

[tamerj1 · Accepted solution]

@homeshagarwal 

Please try

Customer Age =
DATEDIFF (
    RELATED ( 'Customer Lookup'[Date of Birth] ),
    'Sales by Store'[Transaction Date],
    YEAR
)

[homeshagarwal]

Thanks, @tamerj1 !🙌
This was pretty straightforward. Do you know where I messed up?
For some reason, I was trying to add a new column in the Customer's table with this formula earlier 😅😂 so wasn't getting it.

[devanshi · Accepted solution]

Age = DATEDIFF('Table'[Birthdate], 'Table'[Transaction Date],YEAR)

[tamerj1]

Hi @homeshagarwal 
So are looking for a calculated column in the Sales table or a measure in report sliced by transaction? Do you have date of birth column for each customer or just the current age of the customer in years?

[homeshagarwal]

Hi @tamerj1 
I'm looking to include a Calculated Column in my Sales table to specify the customers' age at the time of the transaction. Also, yes, I do have a Birthdate column in the Customer's table.

[tamerj1 · Accepted solution]

@homeshagarwal 

Please try

Customer Age =
DATEDIFF (
    RELATED ( 'Customer Lookup'[Date of Birth] ),
    'Sales by Store'[Transaction Date],
    YEAR
)