Calulate Age

[ktt777]

Hi Everyone

I have below table and i want to use DAX formula to calculate the age of people. 

Name	DOB
A	10/2/1988
B	2/5/1990
C	0
D

but for DOB data is Blank or "0", i want to display as "No Data" 

is there a way to do so ? i try below code : 

Age = if ( Sheet2[DOB ]=""||Sheet2[DOB ]="0","No data", DATEDIFF(Sheet2[DOB ],TODAY(),YEAR))

but it said "DAX comparison operations do not support comparing values of type Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values."

How can i create a Age group formula to categorize 0-18, 18-35, 35-60, over 60 and "No data" group?

thanks . 

[harshnathani · Accepted solution]

@ktt777 ,

Convert the Data Type to Date/Time.

Use a Calculated Column

Age =

IF (NOT(ISBLANK('Table'[DOB ])),

FORMAT(DATEDIFF('Table'[DOB ],Today(),YEAR),"##"),"No Data")

Regards,

Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[harshnathani · Accepted solution]

@ktt777 ,

Convert the Data Type to Date/Time.

Use a Calculated Column

Age =

IF (NOT(ISBLANK('Table'[DOB ])),

FORMAT(DATEDIFF('Table'[DOB ],Today(),YEAR),"##"),"No Data")

Regards,

Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[harshnathani · Accepted solution]

Hi @ktt777 ,

Create another calculated column.

Age Group =

IF('Table'[Age] <> "No Data",

SWITCH(

True(),

VALUE('Table'[Age]) > 60 , "Above 60",

VALUE('Table'[Age]) <=60 && VALUE('Table'[Age]) >30, "31-60",

VALUE('Table'[Age])<=30 && VALUE('Table'[Age]) >18 ,"19-30",

VALUE( 'Table'[Age]) <=18 , "Below 18"

),

"No Data")

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[ktt777]

Wonderful. 

thanks a lot 🙂