In this blog, we will be seeing how we can show the previous 6 months of data from single slicer selection in Power BI.
Regarding the data model, we have created two relationships between the ‘Date’ table and the ‘Sales’ table. The active relationship is using the order data and the inactive relationship is using delivery data as shown below –
Challenge –
What we can use to show the different months. As our Year and Month slicer is from the Date table and is filtering only selected months as shown below –
Solution –
We need to use a different table i.e., we need to create another table containing the same values as the data, but it will not fill the filter coming from the data. In this way, the table will show all the months and then we can restrict the data coming from the newly created table to only show the last 6 months.
Step 01:
We’ll be creating a new table named ‘Previous Date’ that will contain the same values as the data.
Measure
Previous Date = ‘Date’
Step 02:
Now, after creating the ‘Previous Date’ table, we need to create the relationship of the table with the remaining part of the model. Here, we can create an active relationship between ‘Previous Date’ and ‘Sales’. But it’s better to create an inactive relationship between ‘Date’ and ‘Previous Date’ which will be having a many-to-one relationship. This is because we need ‘Previous Date’ to Filter ‘Date’ and not vice-versa. And also, we need full control over the filter that is working on the ‘Previous Date’.
Below is the data model for the same –
Step 03:
Now, we must build the logic and create a measure to go 6 months back from the currently selected date in the date table.
Before building the logic, we need to pay attention to some details –
- The new filter (with 6 months) must replace the active filter on the Date. Therefore, we should remove any filter on the Date.
- The new filter always contains 6 months. We need to intersect the new filter with the filter coming from the visual, which contains a single month.
The below calculation item works together with the ‘Previous Date’ table to show 6 months back from the currently selected date in the ‘Date’ table.
Measure –
Result:
Now we can see the previous 6 months of data from single slicer selection as shown below –
