Get input from slicer

[Anonymous]

hello everyone!

Im tyring to show on a chart sales of each week for 13 weeks. I have table that table contain years and weeks nums , sales. The challlenging part for me I have to get input from user and the chart has to show me the picked weeks and previous 12 weeks. For example if user pick the 11th week of 2024 the chart show me the 52,53 this comes from last year and 1,2,3 ....11 weeks.Im using slicer but its not working Can anyone help me ?

The DAX code of measure:

sales =

VAR __WEEK = SELECTEDVALUE(ShelfAvailability[Week],WEEKNUM(TODAY()))

VAR __13WEEK = __WEEK - 12

VAR __YEAR = SELECTEDVALUE(ShelfAvailability[Year],YEAR(TODAY()))

VAR __RESULT = CALCULATE(SUM(ShelfAvailability[SalesOfWeek]),

                            FILTER(ShelfAvailability,

                                   ShelfAvailability[Week] >= __13WEEK &&

                                   ShelfAvailability[Week] <= __WEEK &&

                                   ShelfAvailability[Year] = __YEAR),ALL(ShelfAvailability))

RETURN

__RESULT

[amitchandak · Accepted solution]

@Anonymous , You need to have week rank column in you date table

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

then you can measure likes

This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
Last 8 weeks = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-8 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

or 13 weeks based on today

Today =
var _max = today()
var _min = _max -7*13 -WEEKDAY(_max ,2) +1
return CALCULATE([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

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
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510