Help with custom column for filtering

[siddrow]

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

[parry2k · Accepted solution]

@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 ""

[siddrow]

Thanks so much - this worked!