Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

relative week filter

Hi, in my report I have bar chart with weeknr as x-axel ex 202223, I want have a weeknr range like from current week+20, example: current week is 202244 so my x axel will be like 202244, 202245,2022...
  • PaulDBrown's avatar
    3 years ago

    Here is one way. 

    Based on this sample data

    I created a dimension table for weeks including an index column which we will use to filter the visual with a measure:

     

    Week Table =
    ADDCOLUMNS (
        VALUES ( 'Table'[Week] ),
        "Index", RANKX ( VALUES ( 'Table'[Week] ), 'Table'[Week],, ASC )
    )
    

     

     

     Create the following measure to add to the filters on the visual, settin the value to equals 1.

     

    Filter weeks =
    VAR _TodayWeek =
        YEAR ( TODAY () ) * 100
            + WEEKNUM ( TODAY () )
    VAR _CurrWeekIndex =
        CALCULATE (
            MAX ( 'Week Table'[Index] ),
            FILTER ( ALL ( 'Week Table' ), 'Week Table'[Week] = _TodayWeek )
        )
    RETURN
        COUNTROWS (
            FILTER (
                'Week Table',
                'Week Table'[Week] >= _TodayWeek
                    && 'Week Table'[Index] <= _CurrWeekIndex + 20
            )
        )
    

     

     

     Sample PBIX file attached