Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Chris2016
Resolver I
Resolver I

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

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.

EligibleDate
011/17/2022
111/17/2022
010/29/2022
110/18/2022
09/30/2022
19/19/2022
08/20/2022
18/1/2022
07/21/2022
17/2/2022
06/22/2022
16/3/2022
05/23/2022
15/4/2022
04/24/2022
14/5/2022
13/25/2022
03/6/2022
12/26/2022
02/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!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

4 REPLIES 4
Chris2016
Resolver I
Resolver I

Hi, Xiaoxin,

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

Chris2016_1-1666681521269.png

 


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!

Chris2016_0-1666680790134.png

 

Anonymous
Not applicable

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

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 🙂
Anonymous
Not applicable

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

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.