How to create a relative Date slicer in Power BI: step by step

João Barros 04 de August de 2026 3 min read

Let's create a relative Date slicer in Power BI that allows the user to choose ranges like Last 7, 30, or 90 days. A relative slicer makes reports dynamic — useful for operational dashboards and trend analysis without having to adjust filters manually. This is especially handy in reports that are consulted daily by teams who need to quickly see performance for the most recent business days: for example, sales summary for the last 7 days or support metrics for the last 30 days.

Prerequisites

Power BI Desktop installed (recent version).
A fact table with a Date or datetime column. Ideally the table has at least 10k–500k rows; if you have millions of rows you may need to optimize the model.
Basic knowledge of Power Query and DAX.

Step 1: Create a calendar table if one doesn't exist

A calendar table is essential to work with relative filters and time functions in DAX. If you already have a Date table, skip to Step 2. Below we create a simple DAX table named Calendar that covers a plausible range (for example 2020–2030). You can adjust the dates to your actual data history.

Calendar =
ADDCOLUMNS(
    CALENDAR(DATE(2020,1,1), DATE(2030,12,31)),
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "yyyy-MM"),
    "DayOfWeek", WEEKDAY([Date])
)

After creating the table, mark it as Date table in Modelagem > Mark as date table and choose the Date column. This improves performance and compatibility with time intelligence functions like TOTALYTD. If your dataset has future dates (e.g., planned orders), make sure the Calendar covers that range.

Step 2: Relate the calendar table to the fact table

Create a relationship between Calendar[Date] and FactTable[Date] (1:*). The relationship should be active and with single filter direction by default. Without the relationship, the slicer will not affect measures correctly. In scenarios with multiple date columns (SaleDate, ShipDate) consider creating inactive relationships and using USERELATIONSHIP in measures when needed.

Step 3: Create a period options table (Period Slicer)

Let's create a manual table with the options we want to present to the user. This simplifies the DAX logic afterwards and allows adding options like "Current month" or "Current quarter" easily.

RelativePeriod =
DATATABLE(
    "PeriodLabel", STRING,
    "DaysBack", INTEGER,
    {
        {"Últimos 7 dias", 7},
        {"Últimos 30 dias", 30},
        {"Últimos 90 dias", 90},
        {"Ano até hoje", 365}
    }
)

Place a slicer on the report with RelativePeriod[PeriodLabel]. I recommend setting the slicer to allow only one selection (single select) if the measure logic assumes a single value; if you want to allow multiple selections, see step 5.

Step 4: Create a measure that applies the relative filter

Instead of filtering all visuals manually, we create a measure that calculates, for example, Sales in the last N days according to the slicer selection. The measure below assumes the temporal context is anchored at the maximum visible date (e.g. today or the most recent date in the dataset).

Vendas Últimos Períodos =
VAR SelectedDays = SELECTEDVALUE(RelativePeriod[DaysBack], 7)
VAR MaxDate = MAX('Calendar'[Date])
VAR MinDate = MaxDate - SelectedDays + 1
RETURN
CALCULATE(
    SUM(FactTable[Vendas]),
    FILTER(ALL('Calendar'), 'Calendar'[Date] >= MinDate && 'Calendar'[Date] <= MaxDate)
)

Explanation: SelectedDays reads the value from the slicer and assumes 7 as the default if nothing is selected. We calculate MinDate and use FILTER(ALL('Calendar')) to ensure the period is applied regardless of other filters in the visual. This also prevents visuals with date axes from being truncated by unwanted filters.

Step 5: Use the measure in visuals and handle common issues

Drag the Vendas Últimos Períodos measure into a card or trend chart. Test with controlled datasets: for example, if your FactTable has 100,000 rows and 10,000 sales in the last 30 days, verify the card shows that total. Common issues and solutions:

SELECTEDVALUE returns BLANK if the user doesn't select anything — we set 7 as the default in the example.
If there are multiple selections in the slicer, the measure may not behave as expected; you can use MAX/MIN to choose a value when multiple selections exist:

-- Exemplo para permitir múltiplas selecções usando o maior período
SelectedDays =
MAX(RelativePeriod[DaysBack])

Other considerations: confirm that the relationship between tables is active; if the dates in the Calendar table do not cover the FactTable range, you will see unexpected values. In large models, avoid using FILTER(ALL('Calendar')) in very frequent measures without optimization, as it can affect performance; alternative: use DATESINPERIOD for scenarios with better performance results.

Verify the result

To confirm it's correct: 1) Select "Últimos 7 dias" and check if the numbers align with the last 7 days of your dataset (e.g.: 1,234 sales); 2) Switch to 30/90 days and observe the proportional change (e.g.: 30 days ≈ 3.5× 7 days, depending on the business); 3) Test with nothing selected — it should use the default value. You can add helper cards with MAX('Calendar'[Date]) and a calculated MIN to see the current range and validate limits.

Conclusion

You implemented a relative Date slicer in Power BI using a period table and a DAX measure that applies the selected range. This approach makes reports more flexible and user-friendly. Next steps: add custom options (current month, quarter), create a measure that respects year-over-year comparisons, or adapt the logic for multiple dates in the fact table. Final tip: always test with boundary dates (start/end of month, leap years) and ensure the Date table covers all required historical and future dates to avoid unexpected cuts in the numbers.