How to calculate 21st birtdate based on Date of Birth coumn then set a reminder flag

[anwilkins]

I am creating a report that shows patients in a program. I want to indicate who is about to age out on their 21st birthday. I have two questions:

What's the DAX to create a column showing the date my clients turn 21

AND, how to set a flag 31 days prior to that date that turns the name record yellow

Ex: but in my report I dont need to display the Flag Date, I just need to use that date but I cant get conditional formating to work on the entire row. 

[VahidDM · Accepted solution]

Hi @anwilkins 

Use this DAX code to create a measure, and download the attached file:

21 Birthday = 
VAR _DOB =
    MAX ( 'Table'[DOB] )
VAR _21B =
    DATE ( YEAR ( _DOB ) + 21, MONTH ( _DOB ), DAY ( _DOB ) ) - 31
RETURN
    IF (
        TODAY () >= _21B
            && TODAY ()
                < DATE ( YEAR ( _DOB ) + 21, MONTH ( _DOB ), DAY ( _DOB ) ),
        "Yellow"
    )

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[anwilkins]

Sorry to say this did not work. Looking at the file I can't tell how to apply the measure and I'd like the entire row highlighted not just that cell. I can do cell highlights with no DAX using conditional formatting if necessary.