Dax for Rolling 5 weeks from selected week and for last 2 Years

[rohanghule12]

Hello All,

I have a sales data which contains fiscal week, Fiscal week Relative, fiscal year, total sales...

I have one scliers which contains Fiscal week Relative. Data is for fiscal year (Feb to Feb).

Suppose I have selected -10 as my relative week from sclier. Now I want to see total sales for last rolling 5 weeks.

And I want to show this is in line chart for last 2 Years (2023 and 2022 in legend).

Need to see week 13,12, 11,10 and 9 in x-axis and sales on Y axis.

Attached image of data and output as example.

 

 

[Anonymous · Accepted solution]

HI @rohanghule12,

If you want the slicer to achieve custom selector effect instead of filter. I'd like to suggest you create an unconnected table as source of slicer. Then you can extract the selection from the slicer and use it as condition in your formulas.

Logic:
Extract the selected value to get the field value less than or equal to selected value -4 to get correspond week number list, then use these number to define the calculation range. 
Use selected value to find out current date and extract the year part to use in if statement to check the filter ranges.(current and previous year)

Regards,

Xiaoxin Sheng