Multiple Calendars for Multiple Companies

[Anonymous]

I have a situation where I need to model multiple companies financial data that each have their own fiscal calendar.  I have tried several approaches and have not solved the problem.  Any help would be appreciated.

[v-xicai]

Hi @Anonymous ,

You may create a calendar table , create relationships with your other tables, then you can use the Calendar[Date] in time intelligent functions, such as YTD, QTD and MTD and so on.

Calendar =CALENDARAUTO() .

Best Regards,

Amy

Community Support Team _ Amy

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

[Tad17]

If your error is putting the months in order for the fiscal year you can create month tables, create a column with numbers to rank your months and then select sort by column and select the number column. Repeat as necessary for each different fiscal year. This will allow you to link different companies to the disferent fiscal year month order tables and it will then sort them by those. Then you can link all of the month number columns to put the data in order if you want though that won't actual match up on a calendar, but at least you can compare the year totals.

[Anonymous]

That is not the problem.  The problem is trying to model a calendar with repeating dates for each fiscal calendar.  Right now, we built such a table and I can't relate it back to my fact table unless I want to live with many-to-many relationships, which I don't.

[ChrisMendoza]

@Anonymous -

Why would you lose unique dates if they are all in 1 calendar table?

The date is the date no matter what you call it.

• Company A calls 01/01/2020 Period 1
• Company B calls 01/01/2020 Period 7
• etc.

You would just make the additional columns that adjust the calendar month number '1' to '7' or whatever you need them to be.

Does that not work?

[Anonymous]

No, that doesn't work because of the requirements of the solution.  Again, I have to store fiscal information for a large number of companies so it doesn't make sense to add separate fiscal columns for each company to the calendar table.  Instead, what we did is add a complete calendar for each company into the same table.  So now our date field is no longer unique. It is only unique if you take the combination of company id and date.

[ChrisMendoza]

@Anonymous -

By your response I presume that at least 1 company does not follow a normal calendar month period, maybe something like 01/31/2020 - 02/29/2020? Otherwise you would only need to add 11 columns to account for any change in what period the date falls into. Is that a correct assumption?

[Anonymous]

I don't know if I understand what you are asking but just to reiterate, I have multiple companies each with their own fiscal calendar that is different.  Currently, these calendars are loaded into one table and the uniqueness of each row is a combination of company id and date.

[ChrisMendoza]

@Anonymous -

A definition of Fiscal Calendar, https://www.investopedia.com/terms/f/fiscalyear.asp. I assumed you meant that your multi-business need falls into 1 of the 12 possible:

FY Version	FY Start	FY End
ver1	01/01/20	12/31/20
ver2	02/01/20	01/31/21
ver3	03/01/20	02/28/21
ver4	04/01/20	03/31/21
ver5	05/01/20	04/30/21
ver6	06/01/20	05/31/21
ver7	07/01/20	06/30/21
ver8	08/01/20	07/31/21
ver9	09/01/20	08/31/21
ver10	10/01/20	09/30/21
ver11	11/01/20	10/31/21
ver12	12/01/20	11/30/21

Since the first FY ver1 type is taken care of by the software as:

FY ver1 type
Period	Period Start	Period End
1	01/01/20	01/31/20
2	02/01/20	02/29/20
3	03/01/20	03/31/20
4	04/01/20	04/30/20
5	05/01/20	05/31/20
6	06/01/20	06/30/20
7	07/01/20	07/31/20
8	08/01/20	08/31/20
9	09/01/20	09/30/20
10	10/01/20	10/31/20
11	11/01/20	11/30/20
12	12/01/20	12/31/20

The subsequent types would follow like:

FY Type 2
Period	Period Start	Period End
1	02/01/20	02/29/20
2	03/01/20	03/31/20
3	04/01/20	04/30/20
4	05/01/20	05/31/20
5	06/01/20	06/30/20
6	07/01/20	07/31/20
7	08/01/20	08/31/20
8	09/01/20	09/30/20
9	10/01/20	10/31/20
10	11/01/20	11/30/20
11	12/01/20	12/31/20
12	01/01/21	01/31/21

etc...

So you're redefining the Period Numbers depending on which month starts the Fiscal Year.

In any of the possibilities, 01/09/2020 is still January 9, 2020 (unique). You are just redefining which period it belongs to within the Fiscal Calendar.

I'll use my own example to try and demonstrate the use case. My organization has the need for three calendars; Academic Calendar, Fiscal Calendar, and Payroll Calendar.

The AY (currently = AY20) and FY (currently FY19) calendars are defined as July 1 through June 30. For the Payroll Calendar I have to adjust "Time Worked", as an example, in June to be accounted for in July of the next FY (FY20). 

I did this with a single column [Date] and just renamed the period and year that it belonged in using additional columns. I also had the added complexity of periods that do not align with a normal calendar month (e.g. 01/31/2020 through 02/29/2020).  You can see how I accomplished the fundamentals at http://cjmendoza.yourweb.csuchico.edu/custom-calendar/create-a-custom-period-calendar-in-power-query/

While you say that your need is complex you have not provided any detail to the complexity; therefore we do not know how to help you without making presumptions. So, what is the worst case Fiscal Calendar that your businesses have? That is probably what you should address first.

[Anonymous]

The complexity comes from having a different calendar for each company.  We are reporting on approximately 300 different companies that may have their own fiscal calendar so we can't just add a few fiscal columns for each company because of the sheer number of companies.  I don't know how to explain it any better than that.

[Tad17]

You may have 300 different companies, but you will not have 300 different fiscal years. Therefore, I would create a table of the companies and assign a variable that groups them based on their fiscal calendar:

Company 1 - Calendar 1

Company 2 - Calendar 1

Company 3 - Calendar 2

Etc.

Then in each of the calendars, you set the dates for the fiscal year be it Jan-Dec, July-June, or even October 15-October 14

Then create a column that simply assigns a number to that day of the fiscal year. For example Calendar 1 fiscal year Jan-Dec

Jan 1 = 1

Jan 2 = 2

...

Dec 30 = 364

Dec 31 = 365

Calendar 2 Fiscal year July-June

Jul 1 = 1

Jul 2 = 2

...

Jun 29 = 364

Jun 30 = 365

then you simply use the number of the day to create you filter or splicer.

It will take some time to create and set up the tables, but given the minimal information you have given us so far (I understand data and information are confidential, we just don't have much to go on) this is the only solution I can think of.

If you have multiple years worth of data you can repeat the process (though it will be simpler) number the years.

[Anonymous · Accepted solution]

This is pretty much what we are doing now.  We have groups and each group has it's own fiscal calendar.  I simply join to the fiscal calendars in my tabular model through my regular calendar on the date key field and then access the fiscal parts of the group's calendar by slicing on the Fiscal Calendar name.  This gives me one calendar to work with given a slice of the group table.  It's working okay and I think this is the direction we are going to take.

[Anonymous]

Is there an example of how you achieved the appropriate result?

[Anonymous]

Hi

Do you perhaps have an example of the solution?

@Tad17 - I think have a similar problem - perhaps you can help me if I provide you with an actual data?

I am struggling to model a scenario where the client has eg 5 companies and some of them have different monthly reporting periods. 

eg some reports on calendar basis 1st to last day of month and some on say 26th - 25th.

Do you have a suggestion on how to handle this so that I can analyse at a client level (aggregating all companies)?

[Tad17]

Can you clear this up for me to make sure I am understanding clearly,

You are pulling all of the data which includes all data for multiple companies that have different fiscal years and you need to separate?

One work around is to go into Eqit Queries

Right click and select "reference". This will create a copy of the query that updates with the actual query.

In the new reference query filter out all of the companies.

Repeat for each company.

This will give you a separate "query" for each company. The best part about using the "reference" option is that it does not actually recreate the query. It simply copies the original query originally and every time it refreshes and then re-performs whatever steps (including filtering) that you apply to it.

From there you can work each company individually while being able to easy build relationships between the tables if necessary.

[Anonymous]

It's not quite that simple.  Essentially, I need to report on lots of companies that each have their own fiscal calendars and I need to be able to model that somehow.  It would not be feasible for me to create separate calendars for each company because there literally could be many companies.  I tried modeling it by adding all the companies fiscal calendars into one table but then I don't have a unique date field anymore and can't use time intelligence to calculate MTD, QTD and YTD for example.  In fact, I don't know how to connect the current calendar to my fact tables other than creating many-to-many relationships which are hard to work with in Power BI/Tabular.  Thanks.

[Tad17]

This is why I suggested creating separate reference tables for each company. Even if it is a lot of companies it is probably your best bet. The only other option I can think of is to create a complex if function that says if company is X and month is X then (month number) and create instances for each fiscal year and each company and simply assign numbers 1-12 instead of dates.