DAX Equation for Table - Need for Date Info to Start on Specific Day

[meredink91]

Hello,

I'm trying to set up a rolling date table and need for it to start on a specific date (April 1, 2024). This is the DAX equation that I was that I have and need help with what needs to be added or modified. Thanks!

DateTAfterApr2024 =
VAR MinYear = YEAR (DATE(2024,04,01 ) )
VAR MaxYear = YEAR ( TODAY())
RETURN
ADDCOLUMNS (
FILTER (
CALENDARAUTO( ),
AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
),
"Calendar Year", "CY " & YEAR ( [Date] ),
"Month Name", FORMAT ( [Date], "mmmm" ),
"Month Number", MONTH ( [Date] ),
"Month Year", format([Date],"mmm yyyy"),
"Month year Sort", format([Date],"YYYYMM"),"Calendar Quarter/Year", QUARTER( [Date] )&"/"&YEAR ( [Date] ))

[Anonymous · Accepted solution]

Hi @meredink91 ,

Thanks for the reply from lbendlin .

Please try this syntax, which I modified based on your measure:

DateTAfterApr2024 =
VAR StartDate = DATE(2024, 04, 01)
VAR EndDate = TODAY()
RETURN
ADDCOLUMNS(
    FILTER(
        CALENDAR(StartDate, EndDate),
        AND(
            YEAR([Date]) >= YEAR(StartDate),
            YEAR([Date]) <= YEAR(EndDate)
        )
    ),
    "Calendar Year", "CY " & YEAR([Date]),
    "Month Name", FORMAT([Date], "mmmm"),
    "Month Number", MONTH([Date]),
    "Month Year", FORMAT([Date], "mmm yyyy"),
    "Month year Sort", FORMAT([Date], "YYYYMM"),
    "Calendar Quarter/Year", "Q" & QUARTER([Date]) & "/" & YEAR([Date])
)

I replaced the CALENDARAUTO function with CALENDAR to create a date table based on the conditions you gave.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[lbendlin]

If it needs to start on a specific day then it is not a rolling table.

You can use CALENDAR("2024-04-01",TODAY())