topic Re: Dynamic column chart 10 weeks in DAX Commands and Tips

Dynamic column chart 10 weeks

BBConsultancy — Wed, 19 Mar 2025 15:55:34 GMT

Hi,

In my Power BI report I want to put a column chart that show the [Sales] of the selected week and 10 weeks prior. I want to select the week using a slicer. I tried multiple things:

1. Measure =

VAR _ChosenWeek = SELECTEDVALUE('Reporting Date'[WeekNr])

VAR _ChosenYear = SELECTEDVALUE('Reporting Date'[Year])

VAR _PriorWeek = _ChosenWeek -1

VAR _MinWeek = _PriorWeek - 10

VAR _MaxWeek = _PriorWeek + 1

RETURN

CALCULATE(

[Sales],

FILTER(

'Reporting Date',

'Reporting Date'[WeekNr] >= _MinWeek &&

'Reporting Date'[WeekNr] <= _MaxWeek &&

'Reporting Date'[Year] = _ChosenYear

)

This measure results in the slicer still being dominant and the chart only showing the selected week.

2. Measure =

VAR _ChosenWeek = SELECTEDVALUE('Reporting Date'[WeekNr])

VAR _ChosenYear = SELECTEDVALUE('Reporting Date'[Year])

VAR _PriorWeek = _ChosenWeek -1

VAR _MinWeek = _PriorWeek - 10

VAR _MaxWeek = _PriorWeek + 1

RETURN

CALCULATE(

[Sales],

FILTER(

ALL('Reporting Date'),

'Reporting Date'[WeekNr] >= _MinWeek &&

'Reporting Date'[WeekNr] <= _MaxWeek &&

'Reporting Date'[Year] = _ChosenYear

)

)

This results in the correct value, but it is all put on the select week. So you'll see one column with the value of all the weeks.

You'll be my hero if you can help me! 🙂

Re: Dynamic column chart 10 weeks

johnt75 — Wed, 19 Mar 2025 16:26:47 GMT

You can use the technique described in https://www.sqlbi.com/articles/show-previous-6-months-of-data-from-single-slicer-selection/

Re: Dynamic column chart 10 weeks

BBConsultancy — Fri, 21 Mar 2025 07:59:43 GMT

Thanks! It definitely pointed me in the right direction. The DATESINPERIOD function doesn't accept weeks unfortunately, but I replaced that part with:

VAR _MinWeek = _ChosenWeek- 10

VAR _MaxWeek = _ChosenWeek+ 1

VAR _DateRange =

FILTER(

ALL('Previous Reporting Date'),

'Previous Reporting Date'[WeekNr] >= _MinWeek &&

'Previous Reporting Date'[WeekNr] <= _MaxWeek &&

'Previous Reporting Date'[Year] = _ChosenWeek

)