topic Re: Dax for Dates RAG Status in DAX Commands and Tips

Dax for Dates RAG Status

AMBP1973 — Sat, 14 Oct 2023 07:24:01 GMT

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.

Re: Dax for Dates RAG Status

Dangar332 — Sat, 14 Oct 2023 07:59:17 GMT

hi, AMBP1973

please refer Here

you get idea how to do conditional formatting using DAX.

if still not get idea provide some data

Re: Dax for Dates RAG Status

AMBP1973 — Sat, 14 Oct 2023 08:34:01 GMT

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

Re: Dax for Dates RAG Status

Dangar332 — Sat, 14 Oct 2023 08:55:56 GMT

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.

Re: Dax for Dates RAG Status

AMBP1973 — Tue, 17 Oct 2023 03:48:55 GMT

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?

Re: Dax for Dates RAG Status

Dangar332 — Tue, 17 Oct 2023 04:52:37 GMT

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

Re: Dax for Dates RAG Status

AMBP1973 — Tue, 17 Oct 2023 05:04:24 GMT

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.

Re: Dax for Dates RAG Status

Dangar332 — Tue, 17 Oct 2023 05:39:05 GMT

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

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

Re: Dax for Dates RAG Status

Dangar332 — Tue, 17 Oct 2023 06:13:42 GMT

is this solution or still not get answer ?

Re: Dax for Dates RAG Status

AMBP1973 — Tue, 17 Oct 2023 08:46:47 GMT

Amazing. Thankyou Dangar332 , really appreciate your assistance 🙂