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.
Can you clear this up for me to make sure I am understanding clearly,
You are pulling all of the data which includes all data for multiple companies that have different fiscal years and you need to separate?
One work around is to go into Eqit Queries
Right click and select "reference". This will create a copy of the query that updates with the actual query.
In the new reference query filter out all of the companies.
Repeat for each company.
This will give you a separate "query" for each company. The best part about using the "reference" option is that it does not actually recreate the query. It simply copies the original query originally and every time it refreshes and then re-performs whatever steps (including filtering) that you apply to it.
From there you can work each company individually while being able to easy build relationships between the tables if necessary.
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.