Forum Discussion
Multiple Calendars for Multiple Companies
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 -
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?
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.
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.
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.
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.