Check if date is between dates (starting from today's date)

[Chris2016]

I need help with the following scenario:
I have a table where 2 columns exist which must be leveraged to create a new calculated column (or a measure, if possible): one column specifying customer's eligibility (1 or 0) and a Date column.

E.g.

Eligible	Date
0	11/17/2022
1	11/17/2022
0	10/29/2022
1	10/18/2022
0	9/30/2022
1	9/19/2022
0	8/20/2022
1	8/1/2022
0	7/21/2022
1	7/2/2022
0	6/22/2022
1	6/3/2022
0	5/23/2022
1	5/4/2022
0	4/24/2022
1	4/5/2022
1	3/25/2022
0	3/6/2022
1	2/26/2022
0	2/27/2022

I need to create a column or a measure that checks if Date is between 6 and 12 months from today's date, and if eligibility is 1. Would it be possible to do this in a measure? 

Thanks!

[Chris2016]

Hi, Xiaoxin,

Thanks a lot for your reply. I tried adding the measure provided, but I get an error. 

As you can see in the screenshot below, I created a column to get the users with 6 to 12 months tenure and eligibility = 1, but the issue is that I need a measure, as I am working with direct connection to AS. In my test pbix (see attached) I was able to add a Calendar table and a new column, but in my actual report I can only add measures.

Many thanks in advance for any tips on how to add a measure instead of a column!

[v-shex-msft · Accepted solution]

Hi @Chris2016,

I think it may be cause with the data model table name conflict with the table function, perhaps you can try to add 'quotation marks' on it to fix this: (abs function will convert the result to positive values, then the expression can check the both previous and next date ranges)

eligibility flag =
VAR currDate =
    MAX ( 'Table'[Date] )
VAR diff =
    ABS ( DATEDIFF ( currDate, TODAY (), MONTH ) )
RETURN
    IF ( diff >= 6 && diff <= 12, 1, 0 )

Regards,

Xiaoxin Sheng

[Chris2016]

Hi, Xiaoxin,

This is brilliant, your suggestion worked. And this gives me what I need:

eligibility flag =

VAR currDate = MAX ( 'Table'[Date] )

VAR diff =  ABS ( DATEDIFF ( currDate, TODAY (), MONTH ) )

var difference =  IF ( diff >= 5 && diff < 12, 1, 0 )

var eligible = SELECTEDVALUE('Table'[Eligible])

return IF(difference && eligible=1, 1,0)

Thanks a lot, this is the solution I needed 🙂

[v-shex-msft]

Hi @Chris2016,

Yes, it is possible. You can create a measure with a variable to extract the current date and compare it with today function in datediff function to get the diff.
After these steps, you can use the if statement to check the result and return the flag.

eligibility flag =
VAR currDate =
    MAX ( Table[Date] )
VAR diff =
    ABS ( DATEDIFF ( currDate, TODAY (), MONTH ) )
RETURN
    IF ( diff >= 6 && diff <= 12, 1, 0 )

Regards,

Xiaoxin Sheng