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 -
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.
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.
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.
Is there an example of how you achieved the appropriate result?
Hi
Do you perhaps have an example of the solution?
Anonymous - 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)?