DAX DateDiff Problem

[fleuhusen]

Hello,

This post is relating DateDiff, Dates and YearFrac. I'm not able to find a solution for this challenge.

I've been looking at a LOT of other posts and a lot of them are talking about columns, and I totally understand that solution but I'm falling into doing something different as I'm using live connections in my model, and cannot add columns to the table of data that I pull from.

As an example, I have data columns that I want to use to calculate their age from their Date of Birth (dob) to a specific date, only using a DAX measure.

The dob is a date hierarchy column, with Year, Quarter, Month and Day in it.

Here is what it looks like:

If I decide to view it as a value, it looks like this:

As the year in the example above is August 1, 1883, what I am looking to do is to calculate the age when the century turned to 1/1/1900 (at this time they would be 16).

The formula I've been trying to use is: DateDiff(Date1, Date2, Year), but Date1 nor Date 2 will not find the dob field already created. YearFrac also isn't working as it is not able to find the variable. 

The challenge I have, is that I have about 3000 records with dates I'm trying to use this formula for, and what I'm trying to figure out is the average age (with 2 decimals) for all the records. It would be great to create another column, but that will not work with the dataset. Ideally, I would use the DAX formula and then average all the results, but I'm not fully sure how to think about it.

Ideally (and I know this wouldn't work), would be to do this: DATEDIFF(DOB, DATE(1900, 1, 1), YEAR).

Would love to hear your tips and thank you for your time.

Best regards,

Fredrik

[TomMartens]

Hey @fleuhusen ,

try using this DAX statement:

DATEDIFF('tablename'[DOB], DATE(1900, 1, 1), YEAR)

Hopefully, this provides what you are looking for.

Regards,

Tom

[TomMartens · Accepted solution]

Hey @fleuhusen ,

if you are creating a measure you have to wrap the reference to the DOB column into an aggregation function, something like this:

DATEDIFF(MIN('tablename'[DOB]), DATE(1900, 1, 1), YEAR)

Of course, you have to choose the proper aggregation function 🙂 but this is the way how measures work.

Regards,

Tom

[fleuhusen]

Thank you @TomMartens . The challenge that comes up is that the DoB column doesn't come up as an option for the DateDiff formula. The DateDiff formula only is seeing other created measures, and no column names. The challenge is that the column has multiple values.

I used your formula:

Measure 3 dob test= DATEDIFF('TableName'[dob], DATE(1900,1,1),YEAR)