Yes/No for Dates with 30 Days

[bchager6]

Hello,

Please see the example table below. All I need to do is create a calculated column where within each cell a "Yes" will be polulated if the Date is within 30 days of today and a "No" will be populated if the date is not within 30 days of today. Please note that the data model I'm working with does not have a date table. I'm pretty sure that I need to use an IF statement along with the Today() function and have a 30 in there somewhere. 

Thanks in advance to anyone who can help!

Project ID	Date
1	4/1/2020
2	3/30/2020
3	5/1/2020
4	6/1/2020

[v-eachen-msft]

Hi @bchager6 ,

You could use ABS() function to return absolute value.

Column =
IF ( ABS ( DATEDIFF ( TODAY (), 'Table'[Date], DAY ) ) <= 30, "yes", "no" )

[az38]

Hi @bchager6 

try a column

Column = IF(TODAY()-30 <= [Date], "yes", "no")

[bchager6]

Thank you. I'm thinking it would be Column =IF(TODAY()+30 <= [Date],"Yes","No")

Adding 30 days to today instead of subtracting. Thoughts?

[az38]

@bchager6 

it depends on what do you mean "within" 🙂 within plus, within minus or within +/- 30 days 🙂

[bchager6]

I'm almost there. The below expression filters a table on projects with start dates beginning today or after today (as opposed to including projects with start dates prior to today), but it is also including projects with start dates beyond 30 days from today. I tried including AND at different points within the expression but can't get it to cooperate.

Within 30 Days of Start Date = IF('Table'[Start Date].[Date]>= TODAY(),IF('Table'[Start Date].[Date] <=TODAY()+30, "Within 30 Days of Start Date","No"))

[az38]

@bchager6 

so, does your statement work? try without .[Date] like

Within 30 Days of Start Date = IF('Table'[Start Date] >= TODAY() && 'Table'[Start Date] <= TODAY()+30, "Within 30 Days of Start Date","No"))

[bchager6]

My statement is working in that it filters on projects with start dates beginning today or after, but it's not truncating the list if start dates are greater than 30 days from today.

Your latest expression isn't working unfortunately. It pulls in start dates both prior to today and after 30 days...

[az38]

@bchager6 

I did check it with my dummy data. it works as appropriated

[bchager6]

Are the semi colons in your code significant? I'm not able to use them...

[az38]

@bchager6
It’s a localization question. In the most common scenario use commas, it’s not the issue here

[bchager6 · Accepted solution]

The code I posted earlier actually does work. Thanks for helping me get there.

Within 30 Days of Start Date = IF('Table'[Start Date].[Date]>= TODAY(),IF('Table'[Start Date].[Date] <=TODAY()+30, "Within 30 Days of Start Date","No"))

[az38]

@bchager6
I’m glad to help you. Good luck!