Forum Discussion

harshagraj's avatar
harshagraj
Post Partisan
6 years ago
Solved

Calculated column for weeks with condition

Hi all please help me to create a calculated column to achieve this with color.

If Finish Date < 5-10 weeks than Milestone then "Green"
If Finish Date < 2-5 weeks than Milestone then "Yellow"
If Finish Date >= than Milestone then "Red"

"


PS: Both are date columns with hierarchy.

Need Help
  • az38's avatar
    az38
    6 years ago

    Hi harshagraj 

    your conditions is completely unclear and not consistent, but i can suggest smth like this

    Column = 
var _datediff = DATEDIFF([Finish Date],[Milestone], WEEK)
RETURN
SWITCH(TRUE(),
_datediff > 5 && _datediff < 10, "Green", 
_datediff > 2 && _datediff < 5, "Yellow", 
_datediff <= 0, "Red",
BLANK()
)

     

2 Replies

  • az38's avatar
    az38
    Community Champion
    6 years ago

    Hi harshagraj 

    your conditions is completely unclear and not consistent, but i can suggest smth like this

    Column = 
var _datediff = DATEDIFF([Finish Date],[Milestone], WEEK)
RETURN
SWITCH(TRUE(),
_datediff > 5 && _datediff < 10, "Green", 
_datediff > 2 && _datediff < 5, "Yellow", 
_datediff <= 0, "Red",
BLANK()
)