Forum Discussion

BBConsultancy's avatar
BBConsultancy
Helper I
1 year ago
Solved

Dynamic column chart 10 weeks

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! 🙂
  • BBConsultancy's avatar
    BBConsultancy
    1 year ago

    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
    )

2 Replies

  • BBConsultancy's avatar
    BBConsultancy
    Helper I
    1 year ago

    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
    )