topic Re: Based on selected fiscal week relative, want to show rolling last 5 weeks data for rolling 2 years in DAX Commands and Tips

Based on selected fiscal week relative, want to show rolling last 5 weeks data for rolling 2 years

rohanghule12 — Fri, 25 Aug 2023 11:14:07 GMT

Need urgent help on DAX - Rolling 5 weeks from selected fiscal relative week and for last two years

Hello,

I have a sales data which contains fiscal week, Fiscal week Relative, fiscal year, fiscal year relative,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.

Very important is I want 2 seperate lines on line chart for 2022 and 2023 with last rolling 5 weeks.

Attached image of data and output as example.

in attached image I have shown one line but I want seperate 2 lines and also shows 2 years in legend.

Re: Based on selected fiscal week relative, want to show rolling last 5 weeks data for rolling 2 years

technolog — Tue, 29 Aug 2023 15:03:04 GMT

To achieve this, you'll need to create a DAX measure that calculates the total sales for the last rolling 5 weeks based on the selected fiscal week relative. Then, you'll use this measure in your line chart to visualize the data for the last two years.

Here's a step-by-step guide:

1. Create a Measure for Total Sales for the Last Rolling 5 Weeks:

Rolling 5 Weeks Sales =
VAR SelectedWeekRelative = SELECTEDVALUE('Table'[Fiscal Week Relative])
VAR MinWeek = SelectedWeekRelative - 4
VAR MaxWeek = SelectedWeekRelative

RETURN
SUMX(
FILTER(
'Table',
'Table'[Fiscal Week Relative] >= MinWeek && 'Table'[Fiscal Week Relative] <= MaxWeek
),
'Table'[Total Sales]
)

2. Create a Line Chart:

- Drag the `Fiscal Week Number` to the X-axis.
- Drag the new `Rolling 5 Weeks Sales` measure to the Y-axis.
- Drag the `Fiscal Year` to the Legend.

3. Filter the Chart:

To ensure that the chart only shows the last 2 years, you can apply a visual-level filter:

- Filter the `Fiscal Year` column to only include the last two years (2023 and 2022).

4. Adjust the X-axis:

To make sure the X-axis only shows the last rolling 5 weeks, you can adjust the X-axis settings:

- Set the minimum value to `SelectedWeekRelative - 4` and the maximum value to `SelectedWeekRelative`.

5. Finalize the Chart:

- Adjust the title, labels, and other formatting options as needed.

Once you've set up the chart, when you select a value from the slicer (e.g., -10), the chart should update to show the total sales for the last rolling 5 weeks for both 2022 and 2023.

The provided DAX code assumes that your table's name is 'Table'. Adjust the table name in the code if it's different.

Re: Based on selected fiscal week relative, want to show rolling last 5 weeks data for rolling 2 years

rohanghule12 — Wed, 30 Aug 2023 06:14:10 GMT

Hello Thank you for your response.

I am able to get last 4 weeks total on line chart, but it is showing only for one year.

In point 3 you have mentioned add year to filter in visual level.
I have tried but it is showing for one year.
I want to see two different lines for two different years.
Which should be dynamic based on fiscal week relative.

Please suggest how can I add fiscal year two lines so I can compare.

I have tried your solution and able to get weeks but not years.

Re: Based on selected fiscal week relative, want to show rolling last 5 weeks data for rolling 2 years

rohanghule12 — Wed, 30 Aug 2023 06:30:09 GMT

It will be helpful if you provide solution in pbix.

Re: Based on selected fiscal week relative, want to show rolling last 5 weeks data for rolling 2 years

rohanghule12 — Thu, 31 Aug 2023 10:29:02 GMT

I have implement your solution in pbix (please find attached pbix).

It is showing only for one week not for rolling 4 weeks and for rolling 2 years.

Re: Based on selected fiscal week relative, want to show rolling last 5 weeks data for rolling 2 years

rohanghule12 — Thu, 31 Aug 2023 10:40:28 GMT

Rolling.pbix

Re: Based on selected fiscal week relative, want to show rolling last 5 weeks data for rolling 2 years

technolog — Sun, 24 Sep 2023 08:10:33 GMT

I hope this message finds you well. I've noticed that this solution remain unresolved. If any of you have managed to find a resolution to the issue, I kindly request that you share your solution for the benefit of the entire community. Alternatively, if you're still facing challenges, please do let us know as well.

Your insights and updates will greatly assist others who might be encountering the same challenge.