Forum Discussion

park's avatar
park
Frequent Visitor
4 years ago
Solved

Conditional formatting by Date periods

Hi,

I would like to do conditional formatting on a table in PowerBI

where if the date is within the past 6 months it is green,

greater then 6 months it is yellow

and no date is orange.

 

Have tried everything and can't get it to work, thanks!

Need Help
  • KNP's avatar
    KNP
    4 years ago

    Hi,

     

    It would be much easier if you posted some sample data.

    Try creating a measure like this (you will need to change references to tables etc.)

    Colour = 
var vToday = TODAY()
var vProcessDate = MIN('Table'[LAST_Process_DATE])
var result = 
SWITCH(
    TRUE(),
    ISBLANK( DATEDIFF(vProcessDate, vToday, MONTH)), "orange",
    DATEDIFF(vProcessDate, vToday, MONTH) > 6, "yellow",
    DATEDIFF(vProcessDate, vToday, MONTH) < 6, "green"
    )
RETURN
result

     

    Assign this to the formatting by field.

     

     

     

     

    See attached PBIX.

     

     

2 Replies

  • KNP's avatar
    KNP
    Super User
    4 years ago

    Hi,

     

    It would be much easier if you posted some sample data.

    Try creating a measure like this (you will need to change references to tables etc.)

    Colour = 
var vToday = TODAY()
var vProcessDate = MIN('Table'[LAST_Process_DATE])
var result = 
SWITCH(
    TRUE(),
    ISBLANK( DATEDIFF(vProcessDate, vToday, MONTH)), "orange",
    DATEDIFF(vProcessDate, vToday, MONTH) > 6, "yellow",
    DATEDIFF(vProcessDate, vToday, MONTH) < 6, "green"
    )
RETURN
result

     

    Assign this to the formatting by field.

     

     

     

     

    See attached PBIX.

     

     

  • park's avatar
    park
    Frequent Visitor
    4 years ago

    I have a table with LAST_Process_DATE as the dates