Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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

unnamed (1).png

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. 

unnamed (2).png

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

unnamed.png

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

Thanks in advance. 

4 REPLIES 4
Aryaja96
Frequent Visitor

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

Click for file 

Hi, @Aryaja96 

Please check the file permissions, I have no access to your dummy data.

Best Regards,
Community Support Team _ Eason

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

amitchandak
Super User
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

 

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.