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

[stefanjt]

Hi, community.

I hope someone can help me as I've been struggling with this for a few days.

The task is simple. I have a calendar table connected to a fact table. I need a line chart showing a value measure (total number of transactions) per week - the YYYY-WW from the Calendar table is on the X axis and the "# of transactions" measure is on the Y axis. I need the line chart to always show the last 3 weeks relative to the last week selected from the date slicer (i.e. if I select week 2024-34, I want to see data for 2024-34, 33 and 32. The same should happen if I choose all weeks in the range [2024-28 - 2024-34]). Here's a sample where I've added a relative date filter for the last 1 year for the whole report and a TOP N filter on YYYY-WW on the line chart by latest Calendar[Date]. When I don't select anything from the date slicer, I am seeing what I want on the line chart:

The issue is that if I select 1 to 2 weeks from the slicer, I am seeing data for fewer than 3 weeks (which is expected considering the setup):

I've tried the following:

1. A measure calculating the "# of transactions" for the last 3 weeks but it seems not to fulfill the purpose as it calculates sales transactions over a rolling 3-week period: 
2. A calculated table based on the calendar table that filters for the last 3 weeks based on MAX(Calendar[YYYYWW])  (my idea was to connect it to the data model and use it for the X axis instead of the original YYYY-WW from the Calendar table) but I understand that it cannot read filter context (e.g. from slicers) and that it is calculated only once upon load. It therefore always shows the last 3 weeks from the Calendar table, regardless of the slicer selection:

I am attaching a sample report. Any suggestions would be greatly appreciated!

Thanks in advance😊

[Sergii24 · Accepted solution]

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! 

[Sergii24 · Accepted solution]

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! 

[stefanjt · Accepted solution]

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]
    )
)