cancel
Showing results for
Did you mean:
Frequent Visitor

## Rolling Weeks

I have a dataset which contains ids of respondents and on which week they were questioned on, i needed to categorize them in some form of 2 and 4 week rolling calculations. something like this

The weeks are for slicer selection with their respective dates and are in a two week period. so if i select week no 34( 27/06-10/07) on slicer, on the left table which currently shows me all the date ranges it should filter something like this.

selecting the slicer for week 34 filtered the table as well, like, starting from the selected two week date range(27/06-10/07), it went back 10 weeks but in a two week rolling period like

Week 34-33, 33-32,32-31,31-30,30-29,29-28,28-27,27-26,26-25,25-24 and shows me the count of respondents accordingly. similarly if i select week 33

it shows me the data for 10 weeks behind that one, and the dates have also changes accordingly, now the bottom row shows the selected date and goes back based on that.

i need to calculate both the 2 week and 4 week rolling periods and show according;y, any help would be appreciated. i have been stuck on this for quite some time now. 😅

4 REPLIES 4
Frequent Visitor

I have attached a dummy data to go along with this. if it helps.

Community Support

Hi, @Aryaja96

Best Regards,
Community Support Team _ Eason

Frequent Visitor

@amitchandak , yes i do have a date table separately created and thank you for the prompt response, going to try this out now.

Super User

@Aryaja96 , if select a date of the week and need more than that, You need and independent date table

Have both table with week rank

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

Last 4 weeks =
var _max = maxx(allselected('Date1'), 'Date1'[week Ranl])
return
CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=_max-4 && 'Date'[Week Rank]<=_max))

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

Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Learn Power BI P&L Statement || Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s ||
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!!! How to get Quick Help !!
Resources | Master Power BI !! Expertise Power BI !! Power BI For Tableau User !!

Announcements

#### Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

#### European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

#### Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors