Forum Discussion
Rolling window in chart
- 10 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 ) )
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.
- MasonMA10 months agoSuper User
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:)