How to Build a Slicer with Preset Date Ranges in Power BI

Being able to view or slice data based on date and time fields is undoubtedly one of the more frequent use cases that any Power BI developer will come across. Usually, this request is to view data based on specific dates, months, and years. But what if you wanted to filter the data using a set of predetermined date ranges? Although this is a fairly common request, the solution could be more straightforward.

In this blog, we will cover how to provide your end-users with a way of filtering the dashboard to specific, pre-determined date ranges.

Use Case

The user wanted to view data for various predetermined date intervals in a report. The specific requirement was that if the user selected the Previous 7 Days parameter (denoted as D-1 in our case), they wanted to see the data for the previous 7 days in a bar chart and yesterday’s data in a card chart. Similarly, for other date intervals such as the past 15 days, past 4 weeks, and past 3 months, the user expected corresponding previous 15 days, previous 4 weeks, and previous 3 months data representations in visualizations such as a bar chart or KPI card.

To meet this requirement, we need to create field parameters for various date ranges that users can select by using a slicer. Additionally, we have created a new calculated table to generate dates corresponding to different date periods. Also, a relationship has been created between the parameter table and the relevant table to facilitate data filtering based on user-selected parameters.

Let’s walk through the blog and explore the step-by-step process.

1. Create a Field Parameter

To create a new field parameter, navigate to the Modeling tab on the top, click on New Parameter, and then select Fields. You will need to specify the date periods and the related data column that will be displayed when the user selects a date period from the slicer, as shown in the figure and code below (feel free to copy and paste the DAX and change the values for your use case).

DAX Code

				
					Parameter =
 {
     ( "D-1", NAMEOF ( Calendar[Date] ), "D-1", 1 ),
     ( "Past 15 Days", NAMEOF ( Calendar[Date] ), "Past 15 Days", 2 ),
     ( "Past 4 Weeks", NAMEOF ( Calendar[Fiscal_Week] ), "Past 4 Weeks", 3 ),
     ( "Past 3 Months", NAMEOF ( Calendar[Fiscal_Month] ), "Past 3 Months", 4 )
 }

2. Create a New Calculated Table

It often requires more work to enable users to analyze data dynamically across different time periods. One powerful way to achieve this in Power BI is by creating a calculated table to create various dates for different date periods. The Date_Periods table is created to do just that.

The below DAX code uses the UNION function to combine dates derived from the calendar [date] table, each represented by a distinct Type, allowing users to select from a range of options such as user wants to see data for the past week, 15 days, 4 weeks, or 3 months and so on.

To create a new calculated table, go through the Modelling tab and click on the New Table option, as shown in the image below.

DAX Code

				
					 Date_Periods =
 UNION (
     ADDCOLUMNS (
         DATESINPERIOD (
             Calendar[CAL_DT],
             MAX ( Calendar[CAL_DT] ),
             -7,
             DAY
         ),
         -- Get the date range for the previous 7 days for sales calc representation in the bar chart.
         "Type", "D-1",
         "Sort," 1 -- Add a column to sort the periods in ascending order
     ),
     ADDCOLUMNS (
         DATESINPERIOD (
             Calendar[CAL_DT],
             MAX ( Calendar[CAL_DT] ) ,
             -15,
             DAY
         ),
         -- Get the date range for the previous 15 days 
         "Type", "Past 15 Days",
         "Sort", 2
     ),
     ADDCOLUMNS (
         DATESINPERIOD (
             Calendar[CAL_DT],
             MAX ( Calendar[CAL_DT] ) ,
             -28,
             DAY
         ),
         -- Get the date range for the past 4 weeks
         "Type", "Past 4 Weeks",
         "Sort", 3
     ),
     ADDCOLUMNS (
         DATESINPERIOD (
             Calendar[CAL_DT],
             MAX ( Calendar[CAL_DT] ) ,
             -90,
             DAY
         ),
         -- Get the date range for the past 3 months
         "Type", "Past 3 Months",
         "Sort", 4
     )
 )

3. Create a Relationship Between the `Parameter` Table, `Date_Periods`, & the `Calendar` Table

Create a relationship between the Parameter table and Date_Periods based on the Type (D-1, Past 15 Days, and so on). Whenever a user selects a parameter type (D-1, Past 15 Days, and others) from the slicer, it will filter the corresponding dates in the Date_Periods table.

This Date_Periods table is related to the Calendar table based on the Date column, and the Calendar table, in turn, is related to the respective fact tables. This relationship allows data filtering in the fact table based on the selected date period. Consequently, it enables the presentation of data in visuals for various date periods, driven by the user’s selection.

4. Display the Last 7 Days Data in a Bar Chart & Yesterday's Data in a Card Chart

Using the previously outlined steps, we could showcase Past 7 days data in a bar chart when the user selects the D-1 parameter type. However, per the requirement, the card visual must show yesterday’s data. To achieve this, we have created a DAX measure, as shown below.

When the user selects D-1, this measure filters the fact table’s date column to TODAY() - 1, which represents yesterday’s data. By placing this measure in the card visual, we can present yesterday’s data by bypassing the past 7 days calculation for D-1, which was derived in the calculated table.

Similarly, for other date ranges, parameters include the past 15 days, past 4 weeks, and past 3 months, representing data in bar charts and card charts for the previous 15 days, previous 4 weeks, and previous 3 months data.

DAX Code

				
					 Safety_Stock_Count =
 IF (
     HASONEVALUE ( Parameter[Parameter_Type] ) // Check if the Parameter table has a single value selected
         && SELECTEDVALUE ( Parameter[Parameter_Type] ) = "D-1",
     // Check if the selected value is "D-1"
     CALCULATE (
         IF (
             ISBLANK ( COUNT ( FACT_Table[Safety Stock] ) ),
             // Check if the count of "Safety Stock" column is blank
             0,
             COUNT ( FACT_Table[Safety Stock] ) // Calculate the count of "Safety Stock"   column
         ),
         FILTER (
             ALL (FACT_Table[ORD_FINISH_DATE] ),
             // Remove any filters on "ORD_FINISH_DATE" column
            FACT_Table[ORD_FINISH_DATE]
                 = TODAY () - 1 // Filter rows where the "ORD_FINISH_DATE" column is yesterday's date
         )
     )
 )

Closing

In this blog, we have delved into an interesting use case we addressed for our valued client. Our exploration of data analysis has revealed the power of using parameters to select date ranges dynamically.

Using Power BI parameters and calculated tables, we simplify data analysis across different time frames. Our solution, with strategically designed relationships and measures, enables users to explore and visualize data effortlessly, meeting our client’s specific and detailed reporting requirements.

If you are seeking assistance for a specific requirement, please contact our team of experts for support, guidance, and best-practice recommendations.

FAQs

What is the use of field parameters in Power BI?

Parameters are used to switch between dimensions that describe metrics, and they are also useful for selecting different date periods.

What is the purpose of a calculated table in Power BI?

A calculated table in Power BI is used to derive data that is useful for specific requirements. Using a calculated table, one can create different dates for various periods and perform other necessary calculations.