Last 5 weeks filter

Hello, I have a table where I have a week column with yyyy-ww format. I do not have any date column. The week column contains data from last year until the next year. So, it is from 2020-01 to 2...

Need Help

v-xiaotang

5 years ago

Hi shamo

Try this,

1. Copy the table as calendar table,

2. Drag the column in Calendar Table into slicer,

3. Then, create the SortColumn in two tables,

In Calendar Table:

SortColumn_Calendar = LEFT('calendar table'[YYYYWW],4)*100+RIGHT('calendar table'[YYYYWW],2)

In Table:

SortColumn_table = LEFT('Table'[YYYYWW],4)*100+RIGHT('Table'[YYYYWW],2)

4. Then, create the measure:

Measure_YYYYWW =
VAR _seldate =
    SELECTEDVALUE ( 'calendar table'[SortColumn_Calendar] )
VAR _startdate =
    MINX (
        TOPN (
            5,
            FILTER ( ALL ( 'Table' ), 'Table'[SortColumn_table] < _seldate ),
            'Table'[SortColumn_table], DESC
        ),
        'Table'[SortColumn_table]
    )
VAR _enddate =
    MAXX (
        TOPN (
            5,
            FILTER ( ALL ( 'Table' ), 'Table'[SortColumn_table] < _seldate ),
            'Table'[SortColumn_table], DESC
        ),
        'Table'[SortColumn_table]
    )
RETURN
    IF (
        ISBLANK ( _seldate ),
        SELECTEDVALUE ( 'Table'[YYYYWW] ),
        IF (
            MIN ( 'Table'[SortColumn_table] ) <= _enddate
                && MIN ( 'Table'[SortColumn_table] ) >= _startdate,
            SELECTEDVALUE ( 'Table'[YYYYWW] ),
            BLANK ()
        )
    )

Result: