How do I create a flexible date range slicer that allows users to switch between monthly quarterly and yearly views

0 votes
How do I create a flexible date range slicer that allows users to switch between monthly, quarterly, and yearly views?

In my Power BI report, I need to offer users a flexible date range slicer that allows switching between monthly, quarterly, and yearly views. I want to make it easy for users to toggle between these timeframes without overcomplicating the slicer options. How can I create an adaptable date range slicer in Power BI to provide multiple time period views?
Nov 12 in Power BI by Evanjalin
• 7,890 points
55 views

1 answer to this question.

0 votes

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.

answered Nov 12 by pooja
• 8,470 points

Related Questions In Power BI

0 votes
0 answers
0 votes
0 answers
0 votes
1 answer

How do I add a filter and a slicer?

Hi, Its OLTP and DW modeling. However, you ...READ MORE

answered May 23, 2019 in Power BI by Avantika
• 1,520 points

edited May 23, 2019 by Omkar 779 views
0 votes
1 answer

Displaying Table Schema using Power BI with Azure IoT Hub

Answering your first question, Event Hubs are ...READ MORE

answered Aug 1, 2018 in IoT (Internet of Things) by nirvana
• 3,130 points
1,350 views
+1 vote
1 answer

Unable to install connector for Power Bi and PostgreSQL

I think the problem is not at ...READ MORE

answered Aug 22, 2018 in Power BI by nirvana
• 3,130 points
2,748 views
+2 votes
2 answers

Migrate power bi collection to power bi embedded

I agree with Kalgi, this method is ...READ MORE

answered Oct 11, 2018 in Power BI by Hannah
• 18,520 points
1,519 views
+1 vote
1 answer

Connect power bi desktop to dataset and create custom reports

Yes using Power BI REST API to ...READ MORE

answered Sep 18, 2018 in Power BI by Kalgi
• 52,350 points
1,668 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP