cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

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

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)

2 ACCEPTED SOLUTIONS
Employee

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.

Proud to be a Datanaut!

Solution Supplier

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

4 REPLIES 4
Solution Supplier

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

Helper I

Thanks this worked perfect!!!

Employee

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.

Proud to be a Datanaut!

Helper I

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors