Forum Discussion
qmestu
11 months agoHelper IV
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(Cu...
- 11 months ago
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 ) )
johnt75
11 months agoSuper User
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.