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