🔄 Show Days Before or After a Selected Date in Power BI Using DAX

Have you ever wanted to dynamically show data for a number of days before or after a date selected by a user in Power BI?

In this article, I’ll show you how to:

• Select a date with a slicer
• Use a what-if parameter to choose a dynamic day range
• Calculate and display values before or after the selected date using DAX

Let’s get started with a hands-on walkthrough using a real Power BI model!

🔍 Problem Statement

You want to analyze data like sales trends, logins, or energy consumption from a few days before or after a date selected by the user.

For example:

• If the user selects June 10, 2024 and chooses 5 as the number of days:
• Show values from June 5 to June 9 (before)
• Or from June 11 to June 15 (after)

This can’t be done natively with slicers. But with a little DAX and Power BI magic — we can do it!

🧱 Step 1: Build a Date Table

Make sure you have a proper date table in your model.

DateTable = CALENDARAUTO()

Once created:

• Mark it as a date table in Model view.
• Build a relationship between DateTable[Date] and your fact table's date column.

🖼️ [Insert screenshot: Date table in Data view]

🎛️ Step 2: Create a What-If Parameter

Go to the Power BI ribbon:
Modeling → New Parameter → Fields:

• Name: Day Offset
• Data type: Whole number
• Minimum: 1
• Maximum: 30
• Increment: 1

Power BI will create:

• A DayOffset table
• A slicer for user input

📅 Step 3: Create a Date Slicer

Drag DateTable[Date] into a slicer visual.

Set it to:

• Dropdown
• Single-select only

This allows the user to select one specific date.

🧠 Step 4: Capture the Selected Date

Create a DAX measure:

SelectedDate = SELECTEDVALUE(DateTable[Date])

This will help us reference the selected date dynamically.

📊 Step 5: Create Measures for Before/After Periods

🔹 Sum for N Days Before the Selected Date

Sales_Before_N_Days = 
VAR N = SELECTEDVALUE('DayOffset'[DayOffset])
RETURN
CALCULATE(
    SUM('Sales'[SalesAmount]),
    FILTER(
        ALL('Sales'),
        'Sales'[Date] < [SelectedDate] &&
        'Sales'[Date] >= [SelectedDate] - N
    )
)

🔸 Sum for N Days After the Selected Dat

Sales_After_N_Days = 
VAR N = SELECTEDVALUE('DayOffset'[DayOffset])
RETURN
CALCULATE(
    SUM('Sales'[SalesAmount]),
    FILTER(
        ALL('Sales'),
        'Sales'[Date] > [SelectedDate] &&
        'Sales'[Date] <= [SelectedDate] + N
    )
)

📈 Step 6: Create Dynamic Visuals

1. Card Visuals to display:

• Total Sales before selected date
• Total Sales after selected date

1. Line or Area Chart:

• X-axis: DateTable[Date]
• Y-axis: Sales[SalesAmount]
• Apply visual-level filters using DAX logic (or add custom date range logic in measures)