Forum Discussion

qmestu's avatar
qmestu
Helper IV
10 months ago
Solved

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...
  • MasonMA's avatar
    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
                )
    )