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.
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.
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.
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)?