M-Query for Date RAG Status

[Sajid02]

Hi Techies, 

i'm looking to add some custom columns to highlight Green, Amber, Red from a Date column in Power BI.

So basically, my date column is called "LastFilledStatus"

Today & Today - 7 = Green

In between before 7 days and before 14 days = Amber

Else Red 

Any help would be greatly appriciated. 

Thanks, 

[v-heq-msft]

Hi @Sajid02 ,
Here is my test data

Create measure

Measure = 
SWITCH(
    TRUE(),
    DATEDIFF(SELECTEDVALUE('Table'[LastFilledStatus]),TODAY(),DAY) = 0 || DATEDIFF(SELECTEDVALUE('Table'[LastFilledStatus]),TODAY(),DAY) = 7,"Green",
    DATEDIFF(SELECTEDVALUE('Table'[LastFilledStatus]),TODAY(),DAY) > 7 && DATEDIFF(SELECTEDVALUE('Table'[LastFilledStatus]),TODAY(),DAY) < 14,"Yellow",
    DATEDIFF(SELECTEDVALUE('Table'[LastFilledStatus]),TODAY(),DAY) < 7 || DATEDIFF(SELECTEDVALUE('Table'[LastFilledStatus]),TODAY(),DAY) > 14, "Red"
    )

Apply to cell elements condition format

Final output 

Best regards,

Albert He

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

[Sajid02]

Hi, 

Just want to highlight that , 09/02 /2024 is coming in Red and 08/02/2024 is in Green . Ideally it should not .

I have tried using this in my case, after the code when i am going in conditional formatting , this measure column is taking as first value only. 

Tried creating a column , it is showing only Green vaue , nothing more.

Please let me know what to do ?

Thanks 

[v-heq-msft]

Hi @Sajid02 ,
Thank you for your reply, as you describe it, my understanding is that you are trying to create a new column to display the colors. If my understanding is correct, can you please provide sample data or pbix file and also please show your desired end result in screenshot or excel so that we can help you better.

Best regards,

Albert He

[Sajid02]

Hi @v-heq-msft 
I have put certain conditions & accordingly I need Rag Status column which i can apply in conditional formatting tab.

Thanks