Forum Discussion
Rolling window in chart
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.
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 ) )
4 Replies
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.
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 ) )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.
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:)
