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