Displaying the Last 6 Months of Data Based on a Single Slicer Selection in Power BI

[anmolmalviya05]

In this blog, we'll explore how to display the previous 6 months of data based on a single slicer selection in Power BI.

Data Model Overview

To achieve this, we create two relationships between the ‘Date’ table and the ‘Sales’ table. The active relationship uses order data, while the inactive relationship uses delivery data, as shown below:

Data Model Configuration: We have configured the relationships so that the active relationship filters based on order data, and the inactive relationship filters based on delivery data.

The Challenge

The challenge is to figure out how to display data for different months using a slicer. In our case, the Year and Month slicer is based on the ‘Date’ table and is currently filtering only the selected months. This is fine for typical month-to-month selection, but we want to display the previous 6 months based on the selected month.

The Solution

To solve this, we will create an additional table that will contain the same month values as the ‘Date’ table but will not be affected by the slicer filter from the ‘Date’ table. This way, we can show all the months and restrict the data to the last 6 months using the newly created table.

Step 1: Create the "Previous Date" Table

We'll begin by creating a new table, ‘Previous Date’, which will contain the same month values as the original ‘Date’ table.

Formula: Previous Date = 'Date'

Step 2: Create Relationships

Next, we need to create a relationship between the newly created ‘Previous Date’ table and the rest of the data model. We’ll set up an active relationship between the ‘Previous Date’ and the ‘Sales’ table. However, we should create an inactive relationship between the ‘Date’ and ‘Previous Date’ tables. This will allow us to filter the ‘Date’ table based on the ‘Previous Date’ table, giving us full control over the filter logic.

Here’s how the data model will look:

Step 3: Build the Filter Logic

Now, we’ll create a measure to calculate the previous 6 months from the currently selected month in the ‘Date’ table. Before we build the logic, keep these points in mind:

• The new 6-month filter must replace the active filter on the ‘Date’ table.
• The filter will always contain the last 6 months of data, so we’ll need to intersect this new filter with the slicer filter, which contains a single selected month.

The following measure works with the ‘Previous Date’ table to show the 6 months prior to the selected date:

Final Result

After implementing the above logic, we can now see the data for the previous 6 months based on a single slicer selection. The solution ensures that only the relevant data for the past 6 months is displayed, as shown below:

I Hope this will help! Appreciate your Kudos !!

Let's Connect on LinkedIn: https://www.linkedin.com/in/anmol-malviya/?originalSubdomain=in