Proper date hierarchy and dynamic date filters can enhance business requirements when building a Power BI report. To achieve this feat, follow the steps below to build a flexible date range slicer for the report that is easier for end-users to operate.
Step 1: Develop a Date Table with More Columns
First, make sure that you have a full Date Table and add a Year, a Month, and a Quarter column. You will also need to insert a distinct column for every month, Quarter, and year. This will be the table on which we will base the slicer.
In Power Query or DAX, prepare a Date Table that includes the following: year, for instance, 2024; Quarter, for example, Q1, Q2, etc.; Month, Jan, Feb, etc.; Period Typology, Monthly, Quarterly, Annual, and so on.
Step 2: Include a Period Type Parameter Table
Then, create a separate parameter table called PeriodSelector to assist users with switching on and off the different periods. It will consist of only one column with the distinct values of “Monthly,” “Quarterly,” and “Yearly,” which will serve as a switch to toggle the period.
PeriodSelector = DATATABLE(
"Period Type", STRING,
{ {"Monthly"}, {"Quarterly"}, {"Yearly"} }
)
Step 3: Use DAX to Create Dynamic Measures
Now, you’ll need to create dynamic measures that adjust based on the selected period. The measure will check the user’s selection from the PeriodSelector table and filter the data accordingly.
- Create a measure to detect the selected period type
SelectedPeriod = SELECTEDVALUE(PeriodSelector[Period Type])
Use this in your main measure to apply the correct filter:
SalesAmountByPeriod =
SWITCH(
TRUE(),
[SelectedPeriod] = "Monthly", CALCULATE(SUM(Sales[SalesAmount]), DATESMTD(DateTable[Date])),
[SelectedPeriod] = "Quarterly", CALCULATE(SUM(Sales[SalesAmount]), DATESQTD(DateTable[Date])),
[SelectedPeriod] = "Yearly", CALCULATE(SUM(Sales[SalesAmount]), DATESYTD(DateTable[Date]))
)
This measure will change depending on what the user selects on the slicer, thus allowing for aggregated data by month, quarter, or year.
Step 4: Build the Slicer and Visuals
Include a slicer in your report using the PeriodSelector table. This will act as a switch between Monthly, Quarterly, and Annual displays. Also, include the Date Table as an additional slicer to restrict the dates to within the selected time frame. Lastly, incorporate the SalesAmountByPeriod measure into your visuals so that the data presented works based on the user's selection.
Step 5: Test and Optimize
Ensure you check the functionality of the slicer to filter out information according to the selected time. This setup should incorporate a child-friendly slicer that will enable the user to toggle easily between monthly, quarterly, and yearly views.
