Dax for Dates RAG Status

[AMBP1973]

Hello Everyone,

I am looking to add conditional formatting to a date column to highlight due dates using Red, Abber and Green. 

My date column is called 'Cancellation Date'. I would like to highlight dates as follows:

Anything due before today = Red

Anything due in the next 6 months = Amber

Anything due in more than 6 months = Green

Any assistance would be much appreciated.

[Dangar332]

hi , @AMBP1973 

try below Dax code  and adjust your table and column name
output = 

var a = DATEDIFF(TODAY(),MAX('Table'[Column1]),MONTH)

return

SWITCH(TRUE(),

a=0,"#FF0000",

a<=6,"#FFBF00",

a>6,"#00FF00"

)

follow below step

chane gradient to field value

choose your measure name and press ok.

you get your result.

refer  Here  to download pbix. file of your requirnment.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly. Appreciate your kudos.

[AMBP1973]

@Dangar332 This is amazing, thankyou so much. It is mainly working well except the dates before today are highlighting yellow instead of red? Any suggestions please?

[Dangar332]

hi, @AMBP1973 

check below it's correct  compare today date . i mention today date(to) in image 

if you still not get your answer provide some data

[AMBP1973]

Hi @Dangar332 , I have checked the DAX I input and it is exactly as you have stated. Here is a snapshot of the cancellation date colomun. Thankyou for your ongoing assistance. 

[Dangar332 · Accepted solution]

hi, @AMBP1973 

try below code 

output =

var a = DATEDIFF(TODAY(),MAX('rag'[date]),MONTH)

var b =

SWITCH(TRUE(),

a<=0,"#FF0000",

a<=6&& a>0,"#FFBF00",

a>6,"#00FF00"

)

return

b

Here you get only red and green combination becuase in your data which you provide none of date lies bw 0 to 6 month .

i already calculate measurte which indicate difference bw today and due date 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly. Appreciate your kudos

[AMBP1973]

Amazing. Thankyou @Dangar332 , really appreciate your assistance 🙂

[Dangar332]

is this solution or still not get answer ?

[Dangar332]

hi, @AMBP1973 

please refer Here 

you get idea how to do conditional formatting using DAX.

if still not get idea provide some data

[AMBP1973]

Hi @Dangar332 . I just thought this could be acheived as I have seen it in a simular question before, but it wasn't quite what I was after. As listed in my question this would be based on a date column called 'Cancellation Date'. I would like to highlight dates as follows:

Anything due before today = Red

Anything due in the next 6 months = Amber

Anything due in more than 6 months = Green

Thankyou in advance