Need measure showing if Date is between Jan 1 of current year and 16 months from Today

[Anonymous]

Hi All,

I need some DAX help. I want to create a measure that will contain either a 1 or a 0 depending on whether dates from my Date table fall between Jan 1 of the current year and 16 months from today's date. I have been trying all sorts of combinations but can't seem to get it to work. For example if today's date is Feb 17, 2020 I would like to test the date values in my Date table to see if they fall between Jan 1, 2020 (beginning of the current year) and June 1, 2021 which is 16 months into the future. Any help is appreciated.

Scott

[VasTg]

@Anonymous 

Try this..

Assuming you want to create the new column in Date dimension itself as you did not provide any info on where you want to create.

Column = 
VAR AA = TODAY()
VAR A = DATE(YEAR(AA),MONTH(AA)+16,DAY(AA))
VAR B = DATE(YEAR(AA),01,01)
RETURN IF(AND('Table'[Date]>=B,'Table'[Date]<=A),1,0)

If it helps, mark it as a solution

Kudos are nice too

[Anonymous]

Thanks. I tried this logic based on yours:

IsWithinYTDPlus16Mths =

VAR AA = TODAY()

VAR A = DATE(YEAR(AA),MONTH(AA)+16,1)

VAR B = DATE(YEAR(AA),1,1)

RETURN IF(AND('Date'[Date]>=B,'Date'[Date]<=A),1,0)

But got this result. It looks like it is giving the number of days in the month for each date:

[Anonymous · Accepted solution]

I guess that logic works at the Day level but I would like to show a 1 or 0 at the specific month level as well. Is there a way to roll it up (only 1 or 0) to the Month level which is really the granularity I want to report on?

[VasTg]

@Anonymous 

You could use another IF on top of the existing one to check if the value is greater than 1.

RETURN IF(IF(AND('Table'[Date]>=B,'Table'[Date]<=A),1,0)>1,1,0)

Replace with above statement and let us know.

If it helps, mark it as a solution

Kudos are nice too 

[Anonymous]

That logic did the trick. I just marked my measure as don't summarize and it worked fine. Thanks again