Forum Discussion

Sajid02's avatar
Sajid02
Helper I
2 years ago

M-Query for Date RAG Status

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, 

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Sajid02
      Helper I
      2 years ago

      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 

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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