conveting text and number in age calculation

[ktt777]

hi,

how can i use dax formula to create column Age with data type as decimal  and the value is blank if the data in Birthday column is blank

then how to calculate average to exclude zero or null ?

Name	Birthday	Age
A	1/1/2022	1.17
B
C	1/12/2022	0.25

thanks

[v-yiruan-msft · Accepted solution]

Hi @ktt777 ,

You can create a calculated column as below to get it:

Age = DIVIDE ( DATEDIFF ( 'Table'[Birthday], DATE ( 2023, 3, 19 ), MONTH ), 12 )

Best Regards

[ppm1]

FYI that the Power Query editor has an Age function on the Add Column tab. That will create a duration column in days you can conver to Total Years. You can then use a measure like this to get the average.

AgeMeasure = CALCULATE(AVERAGE(Person[Total Years]), Person[Total Years] <> 0)

Alternatively, you can avoid adding an extra column at all and just use a measure to calculate it.

AgeMeasure2 =
CALCULATE (
AVERAGEX ( Person, INT ( TODAY () - Person[Birthdate] ) / 365 ),
NOT ( Person[Birthdate] IN { TODAY (), BLANK () } )
)

Pat

[YukiK]

Use a formula like this:

Your Column = DIVIDE( DATEDIFF ( BIRTHDAYCOLUMN, TODAY (), DAY ), 365 )