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,202246...... 202311.

I dont have any date column only one weeknr column like 202021,202022 etc.

Need Help
  • PaulDBrown's avatar
    PaulDBrown
    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

1 Reply

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    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