Need a Date Dimension that does back 9 and 12 months on my Date Table

[medwards807]

I have added 2 Calculated Columns on my dates table (date table as a row for each date 1/1/2014 - 12/31/2017)

IsLast12Months = IF('Dates'[Date] <= TODAY(),IF('Dates'[Date] >= TODAY()-365,TRUE(),FALSE()),FALSE())

IsLast9Months = IF('Dates'[Date] <= TODAY(),IF('Dates'[Date] >= TODAY()-270,TRUE(),FALSE()),FALSE()),FALSE())

This works fine to get be to the exact day from today (i.e. 9 Months 5/15/2016), howewver I need to go back a full 9 months i.e. 5/1/2016.

How can I adjust my Dax Calc Columns to capture all days within my 9 month range <?>

9 Months:

Today = 2/10/2017

1 Jan

2 Dev

3 Nov

4 Oct

5 Sep

6 Aug

7 Jul

8 Jun

9 May (5/1/2016)

[Phil_Seamark · Accepted solution]

Try adding this calculated column to your date table.

Months From Today = IFERROR(DATEDIFF('Dates'[Date],NOW() , MONTH),-1)

This will generate a dynamic column with a number counting it's position to the current month.  Then just set a filter on your Visual, Report or Page to say [Months From Today] between 0 and 9  (or 1 and 9)

This will give you every day in the range you require.

[DoubleJ · Accepted solution]

I solved a similar scenario with the EOMONTH function which returns the last day of the month of a given date.

IsWithinLast9Months = IF('Dates'[Date] > EOMONTH(TODAY(),-9),true,false)

Hope this helps.

JJ

[medwards807]

Thanks this worked perfect!!!

[Phil_Seamark · Accepted solution]

Try adding this calculated column to your date table.

Months From Today = IFERROR(DATEDIFF('Dates'[Date],NOW() , MONTH),-1)

This will generate a dynamic column with a number counting it's position to the current month.  Then just set a filter on your Visual, Report or Page to say [Months From Today] between 0 and 9  (or 1 and 9)

This will give you every day in the range you require.

[medwards807]

Thanks Phil - Your solution worked also, great I received 2 different variations that both worked great!