Date dimension table with rolling 13 months

[JemmaD]

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())

[PaulDBrown]

Try:

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

[JemmaD · Accepted solution]

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

[JorgePinho]

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

[JorgePinho]

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

[JemmaD]

@JorgePinho it says DATEADD must specify a column

[JorgePinho]

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

For reference: https://blogs.perficient.com/2022/08/24/how-to-show-last-refresh-date-in-power-bi/