Best way to add a calendar table with data from sql

[Anonymous]

Hi all. First of all thank you for your help!

 Let me explain to you my current situation:

• Source of the data: Database.
• Date fields: 2, so I'd like to have two calendar tables.
• Is there any calendar table available in the database? Yes.

Ideally I should use the calendar table from my database in terms of performance (The further the better, the join field would be an id rather than a date field, etc) but I'm facing the following issue:

• I'd like to retrieve dates within a range, from the minimum to the maximum of each date field from the fact table. I've tried to do this by referencing the fact table and then choosing the min and max date ( as well explained here) but it increased A LOT the time to refresh the data

That's why I'm wondering if:

• Is there any better way to do this? Having a calendar table with only dates within a range determined by the fact table
• Does anyone know why the time to refresh the data increases a lot while using the MIN and MAX filter? (I've removed that filter and the datasources it's refreshed really fast)

Thanks in advance!

[mahoneypat]

Not totally clear on your scenario.  Unless your source calendar table has special columns in it, I would just make two DAX calendar tables that uses MIN and MAX from your fact table inside the CALENDAR function.  FYI on an article/video that provides DAX (and M) calendar table expressions that also include columns that do not require a sort by column.

No Sort Date Tables! – Hoosier BI

Pat

[Anonymous]

Thanks Pat!

I was thinking about using DAX/M but tried to use the calendar table from my database to improve the performance, but thank you! I'll use your DAX script

About my scenario maybe I didn't explain it well enough:

• I have a sql script that use data from several tables. At the end of the script I get one table (not a physical one) with the data I need
• As the data I need it's not stored in just one fact table I can't filter out the calendar table using min/max from the fact table in the database. That's why I tried to do it in Power BI.
• That means I would have two tables in PBI, the one with the information and the calendar table (with data from so many years)

Tha main thing would be: How could I filter the calendar table based on another table stored in Power BI and not in the database? I could make it but the performance was so bad and the time to refresh increased a lot.

Thanks!

[mahoneypat]

Are you making your 2nd date table with DAX or M?  If M, do you have enable parallel loading enabled (turn off if so you are not pulling your data twice from the database)?  Also, is the date table from your source also related to your fact table (min to max)?  If so, you could use the min/max from the calendar table instead.

Pat

[Anonymous]

Hi Pat, thanks for your answer! About your points:

• I'm using always a connection to the database to pull the calendar table. This calendar table is a generic one related with many fact tables by a numeric id.
• As the two date fields from the fact table would have different min&max I'm pulling my data twice. Is it necessary?
• The calendar table from the database is related with the fact table but the calendar table has all the dates (until 2099 If I recall well) and I just want the ones within a range

Does it help? Thanks in advance