topic Re: Week on week trend to show last 5 weeks based on year ,month and week selections in DAX Commands and Tips

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

ann33 — Thu, 16 Feb 2023 20:33:48 GMT

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.

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

amitchandak — Fri, 17 Feb 2023 14:32:19 GMT

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-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8

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

ann33 — Thu, 23 Feb 2023 15:45:56 GMT

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