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
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
- Sdd15c5 months agoNew Member
Thank you!!