Conditionally format when a date is reached

[anwilkins]

I need to create an alert of any type that shows a patient is about to turn 21. I have the following columns:

Name   /   21st birthday   /   90 Day Alert  

I want the Patient Name cell to either turn yellow or the font change on the date of the 90 day alert and remain highlighted.

Sample:

Name   /   21st birthday   /   90 Day Alert  

Ashley  /   06/15/2022   /     03/15/2022

John    /  11/01/2022   /     08/01/2022

Mary  /   08/22/2022    /    05/22/2022

Thanks in advance

[johncolley · Accepted solution]

Hi @anwilkins ,

Create the following measure, you can change the colours I've specified to whatever you want. 

Colouring = 
var alert = SELECTEDVALUE(Birthday[90 Day Alert])
var result = SWITCH(
               TRUE(),
               alert <= TODAY(), "Red",
               "Black")
return
result

Then in the table formatting settings, select Cell Elements, choose the column you want to format and choose background or font conditional formatting, select Field Value and choose the Colouring measure.

Example output below:

[anwilkins]

Thanks John! I had found a solution by creating another column but I like your measure approach so much more! I'm just starting to use SWITCH TRUE so thanks for sharing this new approach. here's my sad but working solution...three new columns...ugh...

*AlertFlag = if (Now() >= 'MERGE_BillingSUMMARY'[*90 Day Alert],1)

*90 Day Alert = ('MERGE_BillingSUMMARY'[*21st BD Date] - 90)

*21st BD Date = DATE ( YEAR ( MERGE_BillingSUMMARY[Patient Birthdate] ) + 21, MONTH ( MERGE_BillingSUMMARY[Patient Birthdate] ), DAY ( MERGE_BillingSUMMARY[Patient Birthdate] ) )

[johncolley]

Glad it works for you! It's quite a simple, but versatile function particularly for conditional formatting.