Dynamic 4-Week Lookback from One Date Slicer

[Sdd15c]

Hi! I’m trying to make 4 visual tables on one page show metrics for different weeks with the dates being controlled by on slicer.

My fact table is related to my date table through the Reporting Date column, and that Reporting Date is always a Monday (the day the weekly report is pulled).

I want to use a slicer where, when I select a specific Monday:

• Visual Table 1 displays metrics for that selected week.

• Visual Table 2 automatically displays metrics for the week immediately before it (selected week − 7 days).

• Visual Table 3 displays metrics for two weeks prior (selected week − 14 days).

• Visual Table 4 displays metrics for three weeks prior (selected week − 21 days).

When the slicer selection changes, the “current week” table should update to the newly selected week, and each subsequent table should automatically shift relative to that new selection.

All tables have the same measures. Is this possible?

[Xian-Zuo · Accepted solution]

It's possible.

The scenario you describe is essentially a kind of time intelligence, which can be achieved through measurement values ​​and calculation groups.
like this.

Measure = MIN('dim_date'[date])&"~"&MAX('dim_date'[date])

Measure - 7 day = CALCULATE(     [Measure]     ,DATEADD('dim_date'[date],-7,DAY) )

Measure - 14 day = CALCULATE(     [Measure]     ,DATEADD('dim_date'[date],-14,DAY) )

Measure - 21 day = CALCULATE(     [Measure]     ,DATEADD('dim_date'[date],-21,DAY) )

Another way is to write code in a calculation group that can be applied to different measures, which looks like different slicers are used

[cengizhanarslan · Accepted solution]

Step 1) Capture the selected Monday

Selected Week =
SELECTEDVALUE('Date'[Date])

Make sure:

• Your slicer is single-select

• It uses the Date table (not the fact table)

Step 2) Create offset measures

Metric – Week 0 =
VAR SelectedMonday = [Selected Week]
RETURN
CALCULATE(
    [Your Metric],
    'Date'[Date] = SelectedMonday
)

Metric – Week -1 =
VAR SelectedMonday = [Selected Week]
RETURN
CALCULATE(
    [Your Metric],
    'Date'[Date] = SelectedMonday - 7
)

Metric – Week -2 =
VAR SelectedMonday = [Selected Week]
RETURN
CALCULATE(
    [Your Metric],
    'Date'[Date] = SelectedMonday - 14
)

Metric – Week -3 =
VAR SelectedMonday = [Selected Week]
RETURN
CALCULATE(
    [Your Metric],
    'Date'[Date] = SelectedMonday - 21
)

Step 3) Use the correct measure in each table

Table 1 → use Metric – Week 0

Table 2 → use Metric – Week -1

Table 3 → use Metric – Week -2

Table 4 → use Metric – Week -3

Each visual now automatically shifts relative to the slicer selection.

[v-veshwara-msft]

Hi @Sdd15c ,

We wanted to kindly follow up regarding your query. If you need any further assistance, please reach out.
Thank you.

[v-veshwara-msft]

Hi @Sdd15c ,
Thanks for reaching out to Microsoft Fabric Community.

Just wanted to check if the responses provided were helpful. If further assistance is needed, please reach out.
Thank you.

[Ray_Minds · Accepted solution]

Please find the below steps to achieve that 
Fact Table

Date Dimension Table 

Hence, All values matching as per source data 

We can also make it dynamic within a single measure – Please feel free to let us know if you need any help around that 

[Sdd15c]

Thank you!! 

[cengizhanarslan · Accepted solution]

Step 1) Capture the selected Monday

Selected Week =
SELECTEDVALUE('Date'[Date])

Make sure:

• Your slicer is single-select

• It uses the Date table (not the fact table)

Step 2) Create offset measures

Metric – Week 0 =
VAR SelectedMonday = [Selected Week]
RETURN
CALCULATE(
    [Your Metric],
    'Date'[Date] = SelectedMonday
)

Metric – Week -1 =
VAR SelectedMonday = [Selected Week]
RETURN
CALCULATE(
    [Your Metric],
    'Date'[Date] = SelectedMonday - 7
)

Metric – Week -2 =
VAR SelectedMonday = [Selected Week]
RETURN
CALCULATE(
    [Your Metric],
    'Date'[Date] = SelectedMonday - 14
)

Metric – Week -3 =
VAR SelectedMonday = [Selected Week]
RETURN
CALCULATE(
    [Your Metric],
    'Date'[Date] = SelectedMonday - 21
)

Step 3) Use the correct measure in each table

Table 1 → use Metric – Week 0

Table 2 → use Metric – Week -1

Table 3 → use Metric – Week -2

Table 4 → use Metric – Week -3

Each visual now automatically shifts relative to the slicer selection.

[Sdd15c]

Thank you!! 

[Xian-Zuo · Accepted solution]

It's possible.

The scenario you describe is essentially a kind of time intelligence, which can be achieved through measurement values ​​and calculation groups.
like this.

Measure = MIN('dim_date'[date])&"~"&MAX('dim_date'[date])

Measure - 7 day = CALCULATE(     [Measure]     ,DATEADD('dim_date'[date],-7,DAY) )

Measure - 14 day = CALCULATE(     [Measure]     ,DATEADD('dim_date'[date],-14,DAY) )

Measure - 21 day = CALCULATE(     [Measure]     ,DATEADD('dim_date'[date],-21,DAY) )

Another way is to write code in a calculation group that can be applied to different measures, which looks like different slicers are used

[Sdd15c]

Thank you!!