Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Trailing twelve month variable on computed field doesn't update with time slicer

Dear Community, I am confronted with the following situation: Context: I want to create a graph that shows the evolution of my sales over the last 12 months (Trailing Twelve months - TTM) BUT where...
Need Help
Palak_Agarwal's avatar
Palak_Agarwal
Regular Visitor
8 months ago
Step 1: Create a Disconnected Date Table for the Slicer
 
Create a new table for the slicer that won't interfere with the main model:
 
Slicer_MonthYear = 
ADDCOLUMNS(
    CALENDARAUTO(),
    "YearMonth", FORMAT([Date], "MMM YYYY"),
"YearMonth_sort", YEAR(Slicer_MonthYear[Date]) + (MONTH(Slicer_MonthYear[Date])/100)
)
 
YearMonth_sort column to sort YearMonth in slicer
 
Then add:
 
Slicer_Month = STARTOFMONTH([Date])
 
 
This table is not connected to your main Date table — it's used only for the slicer. (As shown in following image)

 

Step 2: Add the Slicer to Your Report
 
Add a slicer visual
 
Use 'Slicer_MonthYear'[Slicer_Month]
 
Set the slicer to Single Select (optional but recommended)
 
Step 3: Build Your Line Chart
 
X-axis: 'Date Dimension'[Date] (from your connected Date table)
 
Y-axis: SUM('Sales'[SalesAmount])
 
Make sure 'Date Dimension' is connected to 'Sales' via the 'Date Dimension'[Date] field.
 
Step 4: Create the Visual Filter Logic
 
Create the following measure to act as a visual-level filter:
 
Sales_Last12Months = 
VAR SelectedDate = MAX(Slicer_MonthYear[Date])
VAR StartDate = EDATE(SelectedDate, -12)
RETURN
IF(
    MAX('Date Dimension'[Date]) >= StartDate && MAX('Date Dimension'[Date]) <= SelectedDate, 
    1, 
    0
)
 
 
This measure checks whether each point on the X-axis falls within the last 12 months based on the selected slicer value.
 
Step 5: Apply Visual-Level Filter to the Line Chart
 
Select the line chart
 
Drag the Sales_Last12Months measure into the Visual Level Filters pane
 
Set the filter to is = 1
 
This filters the line chart to only show the dates in the rolling 12-month window.
 
Result:
 
When the user selects Apr 2023 → chart shows Apr 2022 to Apr 2023
 
When the user selects Dec 2024 → chart shows Dec 2023 to Dec 2024
 
 
If this post helps, please accept this as a solution. Appreciate your kudos.
 
Regards,
 
Palak