Last 5 weeks filter

[shamo]

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 2022-52. I want to create a mesure which shows only data for the last 5 weeks. For example, this is 20th week now and when I activate the filter, it shouls show data for week 15-19 with correct. It should also show the correct year at the end of the year. For example, if I am currently in W3 2022, it should show data for W50 2021 - W2 2022.

Any help or tips are appriciated!

Thanks!

shamo

[v-xiaotang · Accepted solution]

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:

See sample file attached bellow.

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[shamo]

@amitchandak , 

Thanks for the reply. I think this calculates from the last week in the available data. But I want to have last 5 weeks from today. 

I also want to add the measure to filter pane and when I select 1 then it shows the last 5 week and when 0 or not selected then normal data

[amitchandak]

@shamo , make sure you create a separate table for year week  and then add rank column

Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

measures
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

Last 5 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-5 && 'Date'[Week Rank]<=max('Date'[Week Rank])))