Unlock the Power of Relative Date Filtering: Last X Months Based on the Selected Date using DAX

Are you tired of manually updating your date filters in Power BI every time you need to analyze data from the last X months? Look no further! In this article, we’ll delve into the world of DAX (Data Analysis Expressions) and show you how to create a dynamic relative date filter that automatically adjusts to the selected date. Get ready to take your data analysis to the next level!

What is Relative Date Filtering?

Relative date filtering allows you to filter data based on a dynamic time period relative to a specific date or point in time. For example, you might want to see sales data from the last 3 months, or website traffic from the past 6 months. In traditional filtering, you would need to manually update the filter criteria every time the date changes. With relative date filtering, the filter adjusts automatically, saving you time and effort.

Why Use DAX for Relative Date Filtering?

DAX is a powerful formula language used in Power BI, Power Pivot, and other Microsoft Power Platform tools. It provides a flexible and efficient way to create calculations and measures that can be used to filter and analyze data. By using DAX, you can create a relative date filter that is based on the selected date, making it easy to analyze data from any time period.

Creating a Relative Date Filter using DAX

To create a relative date filter using DAX, we’ll need to create a calculated column and a measure. The calculated column will determine the relative date range based on the selected date, and the measure will filter the data accordingly.

// Calculated Column: Relative Date Range
Relative Date Range =
VAR SelectedDate = SELECTEDVALUE('Date Table'[Date])
VAR X = 3 // adjust this value to change the number of months
RETURN
    'Date Table'[Date] >= EOMONTH(SelectedDate, -X) && 'Date Table'[Date] <= SelectedDate

In this code, we’re using the `SELECTEDVALUE` function to get the selected date from the ‘Date Table’. We’re also defining the number of months (X) that we want to filter by. In this example, we’re using 3 months, but you can adjust this value to suit your needs.

The `EOMONTH` function returns the last day of the month, and we’re using it to calculate the start and end dates of the relative date range. The `RETURN` statement then filters the ‘Date Table’ to only include dates within this range.

// Measure: Relative Date Filter
Relative Date Filter =
VAR FilteredData =
    CALCULATE(
        'Table'[Value],
        FILTER(
            'Date Table',
            'Date Table'[Relative Date Range] = TRUE
        )
    )
RETURN
    FilteredData

In this measure, we’re using the `CALCULATE` function to calculate the value of the ‘Value’ column in the ‘Table’ table, filtered by the relative date range calculated in the previous step. The `FILTER` function applies the relative date range filter to the ‘Date Table’, and the `RETURN` statement returns the filtered data.

Using the Relative Date Filter in Power BI

Now that we have our relative date filter measure, let’s see how to use it in Power BI.

1. Drag the ‘Relative Date Filter’ measure to the Filters pane in Power BI.
2. Select the ‘Relative Date Filter’ measure and click the “Filter” icon to apply the filter.
3. Select a date from the ‘Date Table’ to see the filtered data.

As you select different dates, the filter will automatically adjust to show the data from the last X months based on the selected date.

Examples and Variations

Here are a few examples and variations of the relative date filter:

Last 3 Months, Including Current Month

Relative Date Range =
VAR SelectedDate = SELECTEDVALUE('Date Table'[Date])
VAR X = 3
RETURN
    'Date Table'[Date] >= EOMONTH(SelectedDate, -X+1) && 'Date Table'[Date] <= SelectedDate

In this variation, we’re including the current month in the filter by adjusting the start date of the relative date range.

Last 6 Months, Excluding Current Month

Relative Date Range =
VAR SelectedDate = SELECTEDVALUE('Date Table'[Date])
VAR X = 6
RETURN
    'Date Table'[Date] >= EOMONTH(SelectedDate, -X) && 'Date Table'[Date] < EOMONTH(SelectedDate, -X+1)

In this variation, we’re excluding the current month from the filter by adjusting the end date of the relative date range.

Relative Date Filtering with Multiple Date Tables

If you have multiple date tables in your model, you can modify the relative date filter to work with each table separately.

Relative Date Range =
VAR SelectedDate = SELECTEDVALUE('Date Table 1'[Date])
VAR X = 3
RETURN
    'Date Table 1'[Date] >= EOMONTH(SelectedDate, -X) && 'Date Table 1'[Date] <= SelectedDate

Simply create a separate calculated column and measure for each date table, using the corresponding table and date column in each formula.

Conclusion

In this article, we’ve shown you how to create a dynamic relative date filter using DAX in Power BI. With this powerful formula, you can easily analyze data from the last X months based on a selected date, saving you time and effort. By following the steps and examples provided, you can unlock the full potential of relative date filtering in your data analysis.

Whether you’re a seasoned Power BI user or just starting out, we hope this article has provided you with valuable insights and inspiration to take your data analysis to the next level. Happy analyzing!

Keyword	Search Volume	Competition
Relative date filter	210	Low
Last X months based on selected date	50	Medium
DAX relative date filter	320	High
Power BI relative date filter	180	Medium

This article is optimized for the following keywords: “Relative date filter of last X month based on the selected date using DAX”, “DAX relative date filter”, “Power BI relative date filter”, and “Last X months based on selected date”. The search volume and competition for each keyword are provided in the table above.

Frequently Asked Question

Get ready to boost your DAX skills with these FAQs about relative date filter of last X month based on the selected date using DAX!