Forum Discussion

ktt777's avatar
ktt777
Helper V
6 years ago
Solved

Calulate Age

Hi Everyone

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

NameDOB 
A10/2/1988
B2/5/1990
C0
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 . 

DAX
  • Anonymous's avatar
    Anonymous
    6 years ago

    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)
  • Anonymous's avatar
    Anonymous
    6 years ago

    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)

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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)
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        ktt777
        Helper V
        6 years ago

        Wonderful. 

         

        thanks a lot 🙂