Forum Discussion
Dynamic 4-Week Lookback from One Date Slicer
- 5 months ago
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
- 5 months ago
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.
-
- 5 months ago
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
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
- Sdd15c5 months agoNew Member
Thank you!!