Forum Discussion

gauravg3's avatar
gauravg3
Helper I
8 months ago
Solved

RAG Status for Due Date

Hi All,

Pls help advise how to setup RAG status based on due date.

 

If due date is before today, then Red

If due date is between today and 30 days then Amber

If due date is after days then Green

If due date is blank then no due date.

 

I managed to get the Red, Amber & Green status using switch true() calculated column, however I dont know how to add the "no due date" value if the due date is blank.

 

Appreciate your assistance.

 

Thanks

Need Help

6 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    8 months ago

    Hi gauravg3 

     

    Download example PBIX file 

     

    RAG Status = 

SWITCH( TRUE(),

ISBLANK([Due Date]), "No due date"),

[Due Date] < TODAY(), "Red",

[Due Date] >= TODAY()  && [Due Date] < TODAY() + 30 , "Amber",

"Green"

)

     

     

    Regards

     

    Phil

     

  • Praful_Potphode's avatar
    Praful_Potphode
    Super User
    8 months ago

    Hi gauravg3 ,
    Calcualted COlumn Version:

    RAG Status =
SWITCH (
    TRUE(),
    ISBLANK ( 'Project'[DueDate] ), "No Due Date",
    'Project'[DueDate] < TODAY(), "Red",
    'Project'[DueDate] >= TODAY() && 'Project'[DueDate] <= TODAY() + 30, "Amber",
    'Project'[DueDate] > TODAY() + 30, "Green"
)

    above column will give you textual status in visual.

    If you want to use colors for conditional formatting in a visual then you can use this measure version:

    RAG Color Measure =
SWITCH (
    TRUE(),
    ISBLANK ( MAX ( 'Project'[DueDate] ) ), BLANK(),
    MAX ( 'Project'[DueDate] ) < TODAY(), "#FF0000",         // Red
    MAX ( 'Project'[DueDate] ) >= TODAY() && MAX ( 'Project'[DueDate] ) <= TODAY() + 30, "#FFA500",   // Amber/Orange
    MAX ( 'Project'[DueDate] ) > TODAY() + 30, "#008000"     // Green
)

    If these doesn't work, please provider more information on data.

     

    Please give kudos or mark it as solution once confirmed.

     

    Thanks and Regards,

    praful

     

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    8 months ago

      

    RAG Status =
    SWITCH(
    TRUE(),
    ISBLANK('Table'[Due Date]), "No Due Date",
    'Table'[Due Date] < TODAY(), "Red",
    'Table'[Due Date] <= TODAY() + 30, "Amber",
    "Green"
    )

     

    The ISBLANK check must come first since blank dates will also satisfy the other conditions.

     
    If this answer helped, please click Kudos or mark as Solution.
    -Kedar
    LinkedIn: https://www.linkedin.com/in/kedar-pande

    gauravg3

  • v-aatheeque's avatar
    v-aatheeque
    Community Support
    8 months ago

    Hi gauravg3 

    Have you had a chance to look through the responses shared earlier? If anything is still unclear, we’ll be happy to provide additional support.

    • v-aatheeque's avatar
      v-aatheeque
      Community Support
      8 months ago

      Hi gauravg3 

      We wanted to follow up to check if you’ve had an opportunity to review the previous responses. If you require further assistance, please don’t hesitate to let us know.