Forum Discussion

AMBP1973's avatar
AMBP1973
Icon for Helper III rankHelper III
3 years ago
Solved

Dax for Dates RAG Status

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 high...
Need Help -DAX Measures
  • Dangar332's avatar
    Dangar332
    3 years ago

    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
    b
     
    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

AMBP1973's avatar
AMBP1973
Icon for Helper III rankHelper III
3 years ago

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?

Dangar332's avatar
Dangar332
Icon for Resident Rockstar rankResident Rockstar
3 years ago

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

 

  • AMBP1973's avatar
    AMBP1973
    Icon for Helper III rankHelper III
    3 years ago

    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. 

     

     

    • Dangar332's avatar
      Dangar332
      Icon for Resident Rockstar rankResident Rockstar
      3 years ago

      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
      b
       
      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

      • Dangar332's avatar
        Dangar332
        Icon for Resident Rockstar rankResident Rockstar
        3 years ago

        is this solution or still not get answer ?