Issue with Age Calculation Formula in Looker Studio

[lucas_grupoe]

Hello everyone,

I'm facing an issue while trying to calculate the age of clients (in years) based on their date of birth ("Data de Nascimento") field in Google Looker Studio. The field is formatted as dd/mm/yyyy. I’ve tried several formulas to calculate the age, but I keep running into the same outcome.

These are the formulas I've tried:

DATE_DIFF(CURRENT_DATE(), PARSE_DATE('%d/%m/%Y', Data de Nascimento), YEAR)

Other formulas I've tried:

DATE_DIFF(CURRENT_DATE(), PARSE_DATE('%d/%m/%Y', Data de Nascimento), YEAR) 
- IF(
    EXTRACT(MONTH FROM CURRENT_DATE()) < EXTRACT(MONTH FROM PARSE_DATE('%d/%m/%Y', Data de Nascimento))
    OR (EXTRACT(MONTH FROM CURRENT_DATE()) = EXTRACT(MONTH FROM PARSE_DATE('%d/%m/%Y', Data de Nascimento))
        AND EXTRACT(DAY FROM CURRENT_DATE()) < EXTRACT(DAY FROM PARSE_DATE('%d/%m/%Y', Data de Nascimento))),
    1, 
    0)

Despite these formulas being accepted, I’m receiving an error message when I try to use them in a chart: "It was not possible to connect Looker Studio to your dataset. There was a problem retrieving data from the underlying Google Sheet."

I have checked the permissions for the Google Sheets and ensured the date format is consistent.

If anyone has encountered a similar issue or has suggestions on how to resolve this, I would greatly appreciate your help!

Thank you!

[GavinW]

Just to clarify, you can access the other fields in the same sheet?

[lucas_grupoe]

Yes, I have access to the sheets. Initially, I used a column to calculate each client's age, but I would prefer to have the age as a calculated field instead.

[GavinW]

Actually this formula should work for you - if this does not work, I think the issue must be in your gSheet formatting, or possibly from another data source you are blending in?

DATETIME_DIFF(CURRENT_DATE(), YourDateField, YEAR)

[GavinW · Accepted solution]

I tried testing something similar - defined a column in gSheets as a dd/mm/yyyy date format, then used the formula below to calculate the age. It worked for me in Studio. Are you blending data, I wonder? (btw I realise the age calculation is a bit dodgy)

round((date_diff( current_date(), Date of Birth)/365),0)

[lucas_grupoe]

It worked! Thank you so much, GavinW!!!