Last 3 Weeks on a Line Chart - Relative to the Last Week Selected from a Date Slicer

Hi stefanjt, the solution to your problem is in using disconnected table. You need to create a disconnected table that will contain dates (or weeks depending on what you want to filter).

Once you have it, put its value in X axis of the line chart. Now when you select date from Date table slicer, nothing should happen to the chart (because dates between these 2 visuals are disconneted).
Next you need to create a measure that will calcualte the desired metric in a new filter context. This context should be defined by selected value from Date table (save it in variable), therefore similar to this:

Measure =
VAR _SelectedDate = MAX (DateTable[Date)
RETURN
   Calculate(
      SUM (MyTableColumn),
      DiconnectedTable[Date] > _SelectedDate-2,
      DiconnectedTable[Date] <= _SelectedDate
   )

You might also need to add another measure to define what weeks to show (visual level filter). You'd need to test it.

I hope it will nudge you in the right direction 🙂 Good luck on yout project! 

Thanks so much, Sergii24! I've managed to do it and your comment pointed me in the right direction.

I had to create a second Calendar table as you said, then I connected it to the fact table but set the relationship as inactive (this is so that the new measure would get the filter context based on the date from the second calendar table), and then I created a measure like so:

# sales transactions last 3 weeks = 
VAR last_week =
MAX(
    'Calendar'[YYYYWW]
)

RETURN
CALCULATE(
    SUM(
        'Sales fact'[Sales transactions]
    ),
    'Calendar (weekly report)'[YYYYWW] >= last_week - 2 &&
    'Calendar (weekly report)'[YYYYWW] <= last_week,
    USERELATIONSHIP(
        'Sales fact'[Date],
        'Calendar (weekly report)'[Date]
    )
)

 I also had to:

• deactivate the relationship between the main calendar and the fact table so that the fact table is not filtered from the date slicer
• modify the original measure so that it could activate the relationship to the main calendar table, like so:

# sales transactions = 
CALCULATE(
    SUM(
    'Sales fact'[Sales transactions]
    ),
    USERELATIONSHIP(
        'Calendar'[Date],
        'Sales fact'[Date]
    )
)