Quick way to show multiple periods data based on single period selection in slicer

Introduction

We all know that Power BI DAX is very powerful in time intelligent calculation. there are many "out-of-the-box" DAX like DATESYTD, SAMEPERIODLASTYEAR that can give you straightforward result instantly. Yet, there is always case that requires more custom twist. In this article, we’ll explore a very common use case-- a dashboard to allow users to select a specific period and compare data across various periods. i.e. select a single month a return last 13 months data. Let's dig into the solution.

Scenario: Sales Dashboard

Imagine your task is to create a dashboard that visualizes sales performance over time. Users should be able to choose a specific month using a slicer, and the dashboard should dynamically display relevant data for multiple months in a visual like column or bar chart (Figure 1). The problem occurs when a single month is selected, the other month data will be disappeared in the column chart as below because of the filter context (Figure 2).

Requirements

1. Date Slicer: date is from Dim_Date table. in this example, let's assume it is the Month-Year column in Dim_Date.
2. Column Chart: the Y-axis data is from a fact table.

Solution

1. Create another date table like the concept of role-playing dimension, Let's call this new date table as Dim_PresentationDate. Here is the DAX to for it.

Dim_PresentationDate='Dim_Date'        

After you create the new Dim_PresentationDate table, create a relationship with fact table the same way as the existing relationship between Dim_Date and Fact_Sales table.

2. Then Create a Measures for the multiple period you want to show. e.g. let's say you want to create 13 months sales as figure 1. The following DAX use DATESINPERIOD to work out the list of last 13 months as the new filter context and pass that to sales via CALCULATE.

Sales_13_Months  = 
VAR _NumOfMonths = -13
VAR _ReferenceDate = MAX ( 'Dim_Date'[Date] )
VAR _DATESINPERIOD =
    DATESINPERIOD (
        'Dim_Date'[Date],
        _ReferenceDate,
        _NumOfMonths,
        MONTH
    )--Get a list of months that is in the last 13 months of filter context( _ReferenceDate)

RETURN
    
--Remove the existing filter context but keep the new filter context introduced by _DATESINPERIOD 

CALCULATE (
        SUM ( 'Fact_Tale'[MonthlySales] )
        ,REMOVEFILTERS ( 'Dim_Date' )
        ,KEEPFILTERS (  _DATESINPERIOD )
           )        

3. Visual Design. Now let's use the newly create measure in the column chart like below. Be mindful of the Month Year column you pick between slicer and column chart is different as per Figure 4

4. User Interaction. It is all set. Now, if you change the month in the slicer, you will see the change of the column chart to reflect the last 13 months of the selected month. Magic!

Conclusion

What have happened is as there is no relationship between Dim_PresentationDate table and Dim_Date, the filter context doesn't directly apply to the column chart from the slicer until you use the Sales_13_months measure in the column chart. The relationship is then created between these 2 tables using calculate as filter modifier.

This solution is to use role-playing dimension and create relationship from a measure using CALCULATE to modify the period to 13 months rather than 1 months. Remember this is not a rolling total for multiple months (as this will requires slightly different DAX). Rather, it is a rolling monthly figure here.

By following these steps, you’ll create an interactive sales performance dashboard that using a single period slicer to return multiple periods result. There maybe other ways of achieving the same results which I would like to hear from you. Happy dashboard designing! ????