Rolling window in chart

[qmestu]

Hi,

I have generated a calendar table that begins the first day of the year:

CalendarTable = 
VAR CurrentYear = YEAR(TODAY())
RETURN
ADDCOLUMNS(
    CALENDAR(DATE(CurrentYear, 1, 1), DATE(CurrentYear, 12, 31)),
    "year", YEAR([Date]),
    "month", FORMAT([Date], "MMMM"),
    "month (number)", MONTH([Date]),
    "quarter", FORMAT(QUARTER([Date]), "0")&"T"  ,
    "year-quarter", FORMAT([Date], "YYYY") & " " & FORMAT(QUARTER([Date]), "0")& "º Q" ,
    "quarter-year",  FORMAT(QUARTER([Date]), "0")& "º Q"& FORMAT([Date], "YYYY")
) 

How can i use the year-quarter as an X axis in my visuals and have a rolling window, that by default shows the last 4 quarters (without using filters like dropdowns). Right now my visual has, on the X axis:

2025 1Q | 2025 2Q | 2025 3Q | 2025 4Q

What i want is that on the first day of 2026, the value for 2025 1Q doesn't appear on the chart, and the last value is 2026 1Q. For this chart, i have enabled the option to "Show items with no data" as this is a possibility with my data.

Thanks.

[MasonMA · Accepted solution]

Hi, 

Agreed that Power Query would be a better approach. I'd normally use Melissa de korte's Date table function in PQ and it's very customizable. 

https://gist.github.com/m-dekorte/12b53faee9cc1a616fa23f15b1b4a173 

But if you insist on using DAX for this task, complete your logic as below and apply a visual-level filter Flag = 1

CalendarTable = 
VAR CurrentYear = YEAR(TODAY())
RETURN
ADDCOLUMNS(
    CALENDAR(DATE(CurrentYear-1,1,1), DATE(CurrentYear,12,31)),
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMMM"),
    "MonthNumber", MONTH([Date]),
    "Quarter", "Q" & QUARTER([Date]),
    "YearQuarter", YEAR([Date]) & " Q" & QUARTER([Date]),
    "QuarterStart", DATE(YEAR([Date]), (QUARTER([Date])-1)*3 + 1, 1),
    "Flag",
        VAR _currentQStart = DATE(YEAR(TODAY()), (QUARTER(TODAY())-1)*3 + 1, 1)
        VAR _dateQStart = DATE(YEAR([Date]), (QUARTER([Date])-1)*3 + 1, 1)
        RETURN
            IF(
                _dateQStart <= _currentQStart &&
                _dateQStart >= EDATE(_currentQStart, -9),  
                1,
                0
            )
)

[qmestu]

Thank you. I marked your answer as correct. I'm also checking out the link that you provided. How would i use this to achieve the same thing? I've already added the function to my data model.

[MasonMA]

Hi, you can copy her M code and paste them in a blank query in Power Query, input parameters and invoke the function. The rest would be deleting unused columns:) 

[johnt75]

Rather than using DAX to create the table you could use Power Query, there are plenty of articles about creating a date table using Power Query.

Once you have the base table you could add a column checking the Date using Date.IsInPreviousNQuarters and Date.IsInCurrentQuarter.

You'll also need to make sure that you don't start at Jan 1 of the current year, otherwise when we start 2026 all of 2025 will disappear.