Forum Discussion
Dynamic 4-Week Lookback from One Date Slicer
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?
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
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.
-
Please find the below steps to achieve that
Fact TableDate 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
8 Replies
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
Thank you!!
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.
Thank you!!
-
Please find the below steps to achieve that
Fact TableDate 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
Thank you!!
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.Hi Sdd15c ,
We wanted to kindly follow up regarding your query. If you need any further assistance, please reach out.
Thank you.
