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