Bar Chart / Date Calculation

[shivkonar]

Hi,

I am trying to solve a made up problem. Imagine we have a 2 table model. The Sales table and a Date Table in a 1 to Many relationship.

On the Power BI report, there is a slicer for Date[Calendar Year] = 2018 and another slicer for Date[Calendar WeekNumber] and there is a bar chart.

The bar chart always will only show 4 bars, for example. The 4 bars will show [Total Sales] for 4 weeks from the selected Date[Calendar WeekNumber] going backwards.

Eg: User selects Date[Calendar WeekNumber] = 31
Bar chart will show 4 bars:
Bar 1: Total Sales for WeekNumber 28
Bar 2: Total Sales for WeekNumber 29
Bar 3: Total Sales for WeekNumber 30
Bar 4: Total Sales for WeekNumber 31

My approach was a "New Table" route in Power BI. The formula I came up with is as below:

Past 4 Weeks Sales = 
GROUPBY (
    FILTER(
        ALL('Sales'), 
        RELATED('Date'[Calendar WeekNumber]) >= SELECTEDVALUE('Date'[Calendar WeekNumber]) - 3 && 
        RELATED('Date'[Calendar WeekNumber]) <= SELECTEDVALUE('Date'[Calendar WeekNumber])),
    'Date'[Calendar WeekNumber],
    "Total Sales", SUMX ( CURRENTGROUP (), 'Sales'[Sales] )
)

This works when I hardcode the >= with 28 and <= with 31.

Question:
1. What is wrong with my approach? Why does this always return a blank table ? And why does this work with hard-coded conditions
2. Is there a more elegant way to solve this problem?

Thanks,
Shiv