Forum Discussion
Multiple Calendars for Multiple Companies
I have a situation where I need to model multiple companies financial data that each have their own fiscal calendar. I have tried several approaches and have not solved the problem. Any help would be appreciated.
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.
16 Replies
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.
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.
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?
Hi Anonymous ,
You may create a calendar table , create relationships with your other tables, then you can use the Calendar[Date] in time intelligent functions, such as YTD, QTD and MTD and so on.
Calendar =CALENDARAUTO() .
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
