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

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.

3 Replies

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

  • shamo's avatar
    shamo
    Frequent Visitor
    5 years ago

    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

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    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.