Forum Discussion

shamo's avatar
shamo
Frequent Visitor
5 years ago
Solved

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's avatar
    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:

    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.

amitchandak's avatar
amitchandak
Super User
5 years ago

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])))