Calendar table for period with overlapped date

[kalo1314]

Hi all,

I am quite new to the Power Bi and may ask a silly question.  My problem is how to add a number of 12-month periods to the calendar table?  As he dates of the calendar table must be unique, but the periods in a column will be overlapped and induce duplicate dates.  What I want is to make a period slicer to filter other tables by period.

Thanks so much for your help in advance!!

[v-tangjie-msft]

Hi @kalo1314 ,

Thanks to @AnalyticsWizard  and @OwenAuger  for their quick replies.

I have some other thoughts to add:
Power Query is used for cleansing the original data and is a stage of pre-processing the data, so a table or column is created in Power Query and then loaded into PBI Desktop and that table and column is visible, while the calculated columns created in PBI Desktop are part of the processing after the data is pre-processed, so they are not visible in Power Query.

Based on your description, we can create a table in PBI Desktop.

Calendar = 
 var MinDate=...//Your original formula.
 var MaxDate=...//Your original formula.
 RETURN
ADDCOLUMNS(CALENDAR(MinDate,MaxDate),"Year",YEAR([Date]),"Month_Name",FORMAT([Date],"mmm"),"Month_Number",MONTH([Date]),
 "Year_Month",CONCATENATE(YEAR([Date]),CONCATENATE("-",FORMAT([Date],"mmm"))),"Sort_YM",YEAR([Date])*100+MONTH([Date]),
 "Period",SWITCH(TRUE(),
 MONTH([Date])=1,"Jan - Dec 2023",
 MONTH([Date])=2,"Feb 2023 - Jan 2024",
 MONTH([Date])=3,"Mar 2023 - Feb 2024",
 MONTH([Date])=4,"Apr 2023 - Mar 2024"))

If I have misunderstood your needs, please clarify your logic and expected results in a follow-up response.

Best Regards,

Neeko Tang

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

[kalo1314]

Dear Neeko Tang,

Thanks a lot for your help. I now know more about Power Querry, calculated columns, and the function of SWITCH in Dax. I have tried your formula but it seems only can filter one month with the Period.  My desired outcome is to make a calculated column in the calendar table which can have different period.  E.g. The first period can determine the date is from 01/01/2023 to 31/12/2023.  If yes, it shows ''Jan - Dec 2023''.  The second one is one month later, which is from 01/02/2023 to 31/1/2024.  The third one is  from 01/03/2023 to 28/2/2024.  The fourth one is from 01/03/2023 to 31/3/2024.

As you can see, the Periods have overlapped dates.  I don't know how to use SWITCH for that.  Could you give me more help so that I can see the way out?  Your kindness is much more appreciated.

[AnalyticsWizard]

@kalo1314 

Welcome to Power BI! Your question about adding 12-month periods to a calendar table for a period slicer is a good one and certainly not silly—it's a common scenario in many analytical contexts. Here’s a structured way to achieve this without causing issues with duplicate dates.

Step 1: Enhance Your Calendar Table

Firstly, you need to extend your calendar table to include a new column that represents these 12-month periods. There are different ways to define a 12-month period, such as a fiscal year, a moving window, etc. For simplicity, let's assume you want to use a standard fiscal year starting from January. If you need a different setup, you can adjust accordingly.

1. Go to Power Query Editor.
2. Select your Calendar table.
3. Add a custom column to define the fiscal year. If your fiscal year starts in January and ends in December, you can directly use the year of the date. However, if it starts in another month, you might need to adjust the year based on the date. Here's a simple example where the fiscal year starts in January:

if Date.Month([Date]) >= 1 then Date.Year([Date]) else Date.Year([Date]) - 1

4. Name this column something like "Fiscal Year".

Step 2: Create a Period Column

Now, you need to create a column that categorizes each date into a 12-month period. You can use the fiscal year column to help create this. Let's assume you want to label periods like "FY 2020-2021".

"FY " & Text.From(Date.Year([Date])) & "-" & Text.From(Date.Year([Date]) + 1)

This will assign a period label to each date, based on the fiscal year logic.

Step 3: Create a Period Slicer

Once your calendar table has this new Period column:
1. Load the table back into Power BI.
2. Create a slicer visualization.
3. Drag your new Period column into the slicer field.

This slicer now allows users to select periods, and it will filter other visuals/tables according to the selected period. The slicer handles overlapping dates internally by just listing unique period labels.

Step 4: Testing

Ensure your slicer is interacting correctly with other visuals:
- Test by selecting different periods and verifying if the data displayed in other charts/tables changes according to the 12-month period defined.

If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

[kalo1314]

Dear AnalysticsWizard,

Thanks a lot for your help with patience.  I am trying to follow your steps, but I have encountered a problem in the first step. It is because my calendar table is created in the data view and cannot be seen in the Power Query Editor.  My calendar table can be seen below:

Do I need to create it again in Power Query Editor?  My another silly question is: how to do it?  I am quite puzzled about the columns created in the Data View and Power Query Editor.  I don't know when should I use the former and the latter.  Could you teach me?  Thanks a lot.

[OwenAuger]

Hi @kalo1314 

The usual method to handle overlapping period is to create a 'Period' table with a many-to-one bidirectional relationship with you Date table.

The 'Period' table can contain any arbitrary overlapping periods.

Here are a couple of blog posts & video showing how to handle this:

• Creating Current Day, Week, Month And Year Reports In Power BI Using Bidirectional Cross-Filtering A...
• Create Dynamic Periods for Fiscal or Calendar Dates in Power BI
• Custom Date Period Selections in Power BI 

Please post back if needed.

Regards

[kalo1314]

Hi Owen Auge,

Thanks a lot for your reply.  

I have tried your method before by creating the period table to link to the calendar table.  But when I use the period table to create period slicer, the slicer cannot work properly.  As the calendar table links to two tables and the periods cannot link to, is there any other way to put the period data in the calendar table and make the number of periods as a slicer?

I appreciate for your more help!!

[OwenAuger]

@kalo1314 no problem 🙂

Could you provide an example of your requirements?

e.g.

• What items do you want to be available for selection on the slicer?
• What range of dates should relate to each option?

[kalo1314]

Dear Owen Auger,

Thanks for your interest to help me.  I want to use the period slicer to filter 12 month calculation.  There are 4 options:

Jan - Dec 2023

Feb 2023 - Jan 2024

Mar 2023 - Feb 2024

Apr 2023 - Mar 2024

I use it to filter cumulative data in two tables which have been connected to calendar table.  If I create a period table to connect to the calendarcalendar table, the slicer cannot work properly to the two tables.  Therefore, I wonder if there is other way to create period slicer with the calendar table to do so.

Hope that my description is clear and your help can solve it.