Last N Weeks not showing as expected based on slicer selection?

[itsmebvk]

Hi Experts,

I tried many ways and refereed similar threads in this forum, however I am unable to achieve the output I am looking for. I am trying to get last four weeks based on “Year week” selection in slicer for example if I select 202110, I want to see 202107 to 202110.

I tried it different way using the method mentioned below screenshot, but its not working 100%. Yearweek slicer was showing 202190 week 😊which doesn’t make sense.

Can you please see the attached sample file and suggest me better way of achieving this requirement.

Thank you.

[v-kkf-msft · Accepted solution]

Hi @itsmebvk ,

Please create a separate table:

YearWeek = VALUES('Date Table'[Calendar_YearWeek])

Then create the measure:

Rolling_4 = 
VAR yearweektab =
    CALCULATETABLE (
        VALUES ( 'Date Table'[Calendar_YearWeek] ),
        FILTER (
            ALL ( 'Date Table' ),
            'Date Table'[Calendar_Date]
                >= MIN ( 'Date Table'[Calendar_Date] ) - 21
                && 'Date Table'[Calendar_Date] <= MAX ( 'Date Table'[Calendar_Date] )
        )
    )
RETURN
    CALCULATE (
        SUM ( 'Measure'[Measure] ),
        FILTER (
            yearweektab,
            [Calendar_YearWeek] = MAX ( YearWeek[Calendar_YearWeek] )
        )
    )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@itsmebvk , if you select a week and need 4 weeks. Then week slicer should be in an independent date table

You need rank of year week in date 1 and date table

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

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Week Rank])
var _min = _max -4
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Week Rank] >=_min && 'Date'[Week Rank] <=_max))

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8