Forum Discussion

gauravg3's avatar
gauravg3
Helper I
9 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 Gree...
Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    9 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
9 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