Rolling-period analysis is a common requirement in financial and management dashboards. Business users often want to analyze metrics such as Credit Amount, Revenue, Cash In, Cash Out, or Net Cash Flow over different time windows, such as the last 3, 6, or 12 months.
Creating separate visuals or manually changing filters for every period can make a report difficult to maintain. In this implementation, we can create a dynamic solution using a secondary Date table, DAX time-intelligence functions, a disconnected Period Selection table, a SWITCH() measure.
The final report allows users to select a time period and dynamically change the metric being analyzed without creating multiple versions of the same visual.
1. Designing the Date Model
The first step is to create the required date structure.
In this implementation, the model contains:
- Date Table – the primary date table used for the report’s normal date filtering.
- SecondDate – a secondary date table used to create the alternate date context for rolling-period calculations.
The relationship between Date Table and SecondDate is configured in the model and is used inside the calculation through USERELATIONSHIP().
The important distinction is that SecondDate is not the disconnected table.
The disconnected table will be created separately for selecting the required period.
The overall concept is:
Date Table(many side)
│
│ Relationship
▼
SecondDate(one side)
The Period Selection table, on the other hand, remains disconnected from the model.
2. Creating the Last 3-Month Calculation
The first calculation can be created for the last three months.
Last 3M =
VAR referencedate =
MAX(‘Date Table'[Date])
VAR previousdates =
DATESINPERIOD(
‘SecondDate'[Date],
referencedate,
-3,
MONTH
)
VAR result =
CALCULATE(
SUM(G_Lentries[Credit_Amount]),
REMOVEFILTERS(‘Date Table’),
KEEPFILTERS(previousdates),
USERELATIONSHIP(
‘SecondDate'[Date],
‘Date Table'[Date]
)
)
RETURN
result
There are several important steps happening in this measure.
Getting the Reference Date
VAR referencedate =
MAX(‘Date Table'[Date])
This identifies the latest date available in the current report context.
For example, if the report is filtered up to 31 March 2026, this variable returns 31 March 2026.
This becomes the anchor date for the rolling calculation.
Creating the Rolling Date Range
VAR previousdates =
DATESINPERIOD(
‘SecondDate'[Date],
referencedate,
-3,
MONTH
)
DATESINPERIOD() generates a table containing the required date range.
Here, -3 tells Power BI to move three months backward from the reference date.
The important point is that DATESINPERIOD() returns a table of dates, which can then be used as a filter inside CALCULATE().
3. Controlling the Filter Context
The calculation then uses:
CALCULATE(
SUM(G_Lentries[Credit_Amount]),
REMOVEFILTERS(‘Date Table’),
KEEPFILTERS(previousdates),
USERELATIONSHIP(
‘SecondDate'[Date],
‘Date Table'[Date]
)
)
REMOVEFILTERS()
REMOVEFILTERS(‘Date Table’)
The existing Date Table filter is removed so that it does not conflict with the rolling date range generated by DATESINPERIOD().
KEEPFILTERS()
KEEPFILTERS(previousdates)
The three-month date range is then applied to the calculation while preserving compatible filters in the existing context.
USERELATIONSHIP()
USERELATIONSHIP(
‘SecondDate'[Date],
‘Date Table'[Date]
)
This temporarily activates the specified relationship for the calculation.
This allows the measure to use the secondary date context without permanently changing the model’s active relationship behavior.
4. Creating 6-Month and 12-Month Measures
The same pattern can be reused for different periods.
For the last 6 months:
Last 6M =
VAR referencedate =
MAX(‘Date Table'[Date])
VAR previousdates =
DATESINPERIOD(
‘SecondDate'[Date],
referencedate,
-6,
MONTH
)
RETURN
CALCULATE(
SUM(G_Lentries[Credit_Amount]),
REMOVEFILTERS(‘Date Table’),
KEEPFILTERS(previousdates),
USERELATIONSHIP(
‘SecondDate'[Date],
‘Date Table'[Date]
)
)
For the last 12 months:
Last 12M =
VAR referencedate =
MAX(‘Date Table'[Date])
VAR previousdates =
DATESINPERIOD(
‘SecondDate'[Date],
referencedate,
-12,
MONTH
)
RETURN
CALCULATE(
SUM(G_Lentries[Credit_Amount]),
REMOVEFILTERS(‘Date Table’),
KEEPFILTERS(previousdates),
USERELATIONSHIP(
‘SecondDate'[Date],
‘Date Table'[Date]
)
)
Now we have three reusable calculations:
Last 3M → Last 6M → Last 12M
But we still need a way for the user to select which one should be displayed.
5. Creating a Disconnected Period Selection Table
Create a new table using:
Period Selection =
DATATABLE(
“Period”, STRING,
{
{“Last 3 Months”},
{“Last 6 Months”},
{“Last 12 Months”}
}
)
No relationship should be created between this table and the other tables.
This is the actual disconnected table in the solution.
Add Period Selection[Period] to a slicer. When the user selects a period, the selection itself does not filter G_Lentries.
Instead, DAX will read the selection and decide which measure should be returned.
6. Creating the SWITCH Measure
Now we create the measure that controls the final output:
Selected CA =
SWITCH(
SELECTEDVALUE(‘Period Selection'[Period]),
“Last 3 Months”, [Last 3M],
“Last 6 Months”, [Last 6M],
“Last 12 Months”, [Last 12M],
[Last 12]
)
SELECTEDVALUE() retrieves the period selected by the user.
The SWITCH() function then evaluates that selection:
Last 3 Months → Last 3M
Last 6 Months → Last 6M
Last 12 Months → Last 12M
The final [Last 12M] acts as the default result when no single period is selected.
This means the visual only needs to use Selected value, rather than placing three different measures into separate visuals.
Conclusion
This approach demonstrates how multiple Power BI features can work together to create a scalable analytical experience.
The secondary SecondDate table provides an alternate date context, DATESINPERIOD() generates the rolling time window, REMOVEFILTERS() and KEEPFILTERS() control filter propagation, and USERELATIONSHIP() activates the required relationship during calculation.
The disconnected Period Selection table captures the user’s 3-, 6-, or 12-month choice, while the SWITCH() measure determines which calculation should be returned.
Finally, Field Parameters can be added to allow users to dynamically switch between business metrics.
The result is a single interactive visual capable of answering multiple analytical questions without creating separate visuals for every metric and time period combination.
Author By
Rajeshwari Sharma
Rajeshwari is an experienced data professional with a track record of using data-driven insights to improve business performance. She has successfully managed multiple projects, utilising her expertise in data analysis and database administration. As a Microsoft-certified Data Analyst and Azure Database Administrator, she has cultivated a deep understanding of data management best practices and advanced analytics techniques. Her MBA in Business Analytics has equipped her with a solid foundation for integrating business strategy with data insights.
