cancel
Showing results for
Search instead for
Did you mean:
Resolver I

## Date dimension table with rolling 13 months

I'm trying to create a date table which only goes back as far as the last 13 calendar months from today and my code does not work, can you show me where i'm going wrong?

Date = CALENDAR(DATE(MONTH(TODAY()-13),1,1),TODAY())

1 ACCEPTED SOLUTION
Resolver I

I found a simpler solution which corrected my original syntax
Date = CALENDAR(DATE(YEAR(TODAY()),MONTH(TODAY())-13,DAY(TODAY())),today())

6 REPLIES 6
Super User

Try:

``````13 months Cal =
VAR _Today =
TODAY ()
VAR __13Months =
DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 13, DAY ( TODAY () ) )
RETURN
CALENDAR ( __13Months, _Today )
``````

Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.

Resolver I

I found a simpler solution which corrected my original syntax
Date = CALENDAR(DATE(YEAR(TODAY()),MONTH(TODAY())-13,DAY(TODAY())),today())

Solution Sage

Interesting solution. I didn't know that using Date() would automatically change the year based on the subtraction of the months 🙂

Solution Sage

Try this: Date = CALENDAR(DATEADD(TODAY(),-13,MONTH),TODAY())

Resolver I

@JorgePinho it says DATEADD must specify a column

Solution Sage

Create a Last Refresh column for your report and use that column.

## Helpful resources

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors