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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ann33
Frequent Visitor

Week on week trend to show last 5 weeks based on year ,month and week selections

Hi All,

 

I have a date column from which I need to plot a line graph with weeks in axis.I ahve year,month and week filter.If the selections are year-2022,month- Feb ,Week - WK7,then graph should show WK3,WK4,WK5,WK6,WK7.

 

Requesting the guidance.

2 REPLIES 2
amitchandak
Super User
Super User

@ann33 , if you want to select a week and then display 5 weeks , you need two date table on joined and another one independent for slicer

 

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

 

 

For week rank, create new column in both date tables

 

Have these new columns in Date Table, Week Rank is Important in Date/Week Table

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

 

 

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

Thank you But the other charts in the report should show only the selected week data.Can we achieve this along with the 4 weeks trend using a single slicer

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors