Create sections in Power BI Desktop based on date column

[micheledubose]

I'm creating a report that needs to display the number of phone calls within a date range and a specific phone number. I have a page filter of date and phone number. The date range can vary depending on what the end user chooses.

Month	Day	Count of Phone Number
12/2020	12/01/2020	4
	12/09/2020	1
	Month Total	5

Month	Day	Count of Phone Number
1/2021	1/8/2021	2
	Month Total	2

Month	Day	Count of Phone Number
2/2021	2/05/2021	1
	Month Total	1

Instead I have this:
 

Month	Count of Phone Number
12/1/2020	4
12/9/2020	1
1/8/2021	2
2/05/2021	1
Total	8

Is it possible to separate the Month in sections? I know I can filter on the visual by selecting the date range that I want. However, as mentioned, the date range can vary depending on what the end user chooses. So, I'm unsure how to "dynamically" create such a filter within the report.

[micheledubose · Accepted solution]

I was able solve my issue by using the Matrix visualization. I added the Month and Day columns as Rows and Count of Phone Number column as Values. As a result, I was able to get the "break/split" functionality that I was looking for.

[v-henryk-mstf]

Hi @micheledubose ,

Based on the information you provide, do you want to separate the month in the date, and then filter the visual based on the month? If so, please refer to my following steps.

You can separate the month in the Date in the power query, and then put the month field in the "filter" to filter data, and get below result:

If the problem is still not resolved, please provide detailed error information. Let me know immediately, looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[micheledubose]

Hi @v-henryk-mstf ,

I forgot to mention that I'm connecting to an Analysis Server database.  Therefore, the steps you've mentioned are unavailable to me in Power BI Desktop.

[v-henryk-mstf]

Hi @micheledubose ,

Try to create a calculated column to get the month in the date.

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[micheledubose]

Hi @v-henryk-mstf ,

I was able to create the calculated column to get the month in the date.  In my report, I've added a slicer similar to your screenshot and get the expected results.  However, if I want to show a table with 12/2020 data as well as another table with 1/2021 data, do I need to create n numbers of table visuals manually?  Also, with the slicer, it affects all visuals within the report.  So, with the slicer, I choose Month 1 and my tables will show data for Month1 only.  I want to show data for Month 1 in one table and data for Month 12 in another table.

What I'm expected is this:

Table1

Date |  Count of Phone Number

12/1/2020  4

12/2/2020  5

Total           9

Table2

Date |  Count of Phone Number

1/4/2021   1

Total          1

[v-henryk-mstf]

Hi @micheledubose ,

According to your needs, you can try the "Edit interaction" function in the Format window of the desktop.

After selecting the visual you need to interact with, the first slicer filters the value of month as 2 and the second slicer filters the value of month as 12, refer to the following:

If you want to learn more about this feature, you can refer to the official document: Change how visuals interact in a report - Power BI | Microsoft Docs

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[micheledubose]

Hi @v-henryk-mstf ,

This is a cool feature but still requires manually adding multiple tables then using a slicer to filter the data.  I need to accomplish the one table being split automatically/dymanically.  I want to insert a break/split to divide the table into many mini tables based on Date column. In other words, if the date range in the page filter is 1/1/2021-1/31/2021, I expect to see data 1 months worth.  If the date range in the page filter is 12/1/2020-1/31/2021, I expect to see data 2 months worth.  

So, I have this with the scenario of getting data from 12/1/2020-1/31/2021:

Date |  Count of Phone Number

12/1/2020  4

12/2/2020  5

1/4/2021   1

Total           10

I want this with the scenario of getting data from 12/1/2020-1/31/2021:

Date |  Count of Phone Number

12/1/2020  4

12/2/2020  5

Total           9

Date |  Count of Phone Number

1/4/2021   1

Total          1

[lbendlin]

You will want to add a calendar table to your data model. Then you can apply that to your sections.