Forum Discussion

siddrow's avatar
siddrow
Helper III
1 year ago
Solved

Help with custom column for filtering

Hi

 

I've created the below custom column which I will then use to create a measure to switch the colour of the font in a visual (1 week = red, 2 weeks = orange, 1 month = red) and also use as a filter to only show on 1 week, 2 weeks and 1 month data in my visual.

 

if [Due Date]-[#"Today's Date"] <=7 and [#"Total % Complete"] = 0 then "1 Week"
else if [Due Date]-[#"Today's Date"] >7 and [Due Date]-[#"Today's Date"] <=14 and [#"Total % Complete"] <0.8 then "2 Weeks"
else if [Due Date]-[#"Today's Date"] >14 and [Due Date]-[#"Today's Date"] <=30 and [#"Total % Complete"] <0.9 then "1 Month"
else ""

 

I'm getting the below error. Due date and today's date is in date format, total % complete is % format. 

 

 

What's the alternative solution to be able to do what I need to do here?

 

thanks

Need Help
  • parry2k's avatar
    parry2k
    1 year ago

    siddrow try this:

     

    if Duration.Days([Due Date]-[#"Today's Date"]) <=7 and [#"Total % Complete"] = 0 then "1 Week"
else if Duration.Days([Due Date]-[#"Today's Date"]) >7 and Duration.Days([Due Date]-[#"Today's Date"]) <=14 and [#"Total % Complete"] <0.8 then "2 Weeks"
else if Duration.Days([Due Date]-[#"Today's Date"]) >14 and Duration.Days([Due Date]-[#"Today's Date"]) <=30 and [#"Total % Complete"] <0.9 then "1 Month"
else ""

2 Replies

  • parry2k's avatar
    parry2k
    Super User
    1 year ago

    siddrow try this:

     

    if Duration.Days([Due Date]-[#"Today's Date"]) <=7 and [#"Total % Complete"] = 0 then "1 Week"
else if Duration.Days([Due Date]-[#"Today's Date"]) >7 and Duration.Days([Due Date]-[#"Today's Date"]) <=14 and [#"Total % Complete"] <0.8 then "2 Weeks"
else if Duration.Days([Due Date]-[#"Today's Date"]) >14 and Duration.Days([Due Date]-[#"Today's Date"]) <=30 and [#"Total % Complete"] <0.9 then "1 Month"
else ""