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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.