Dynamic column chart 10 weeks

[BBConsultancy]

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! 🙂

[johnt75]

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

[BBConsultancy · Accepted solution]

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

)