Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
An in-memory calendar table can be created in many difference ways in Power BI. Here are a few:
1. Create the calendar table in the source system (e.g. SQL) and import it into Power BI.
2. Create the calendar table using M inside Power BI
3. Create the calendar table using "Enter data" in Power BI
4. Create the calendar table using "New table" and the using the DAX-function CALENDAR.
4.a. ...and add all additional columns (Year, Month, Quarter etc.) using DAX-function ADDCOLUMNS.
4.b. ...or add all additional columns (Year, Month, Quarter etc.) using "New Column".
Is there any performance difference between these alternatives?
(FYI I find the most convenient calendar table being alternative 4.b, because then it's easy to add/remove/modify the alternative columns without breaking relationships, "Sort by Column" etc. and it's also easy to clone the table including all columns by simply using "New table": CalendarB = Calendar A.)
Hi @Anonymous
First, thank you for sharing different ways to create a calendar table.
1.
the calendar table is static for way 1 and 3, for way1, to make the calendar table change, you need to change in data source and then refresh from Power BI Desktop.
So, way 3 is not recommended.
If you have a SQL Server data warehouse, you can use SQL to create date dimension tables. It’s usually best to unify all of the reporting data in the data warehouse or data mart to create a single version of the truth for reporting.
2.Regarding way4
Obviously 4.b. is most recommended.
3.As for way2 and way4
It is hard to say which is better, They have their own advantages,
It is uncompariable, it depends on the your requirements.
We could take a look at the articles below:
Calculated table in power bi desktop which explains the main difference between calculated table and table created in power query.
DAX VS M which explains the main difference between M and DAX
SQL, DAX, or M which explains how to create a calendar table with three methods and their advantages.
Best Regards
Maggie
The advantage of 4a is it gives you one formula that you can easily replicate between reports.
If you have a column setup that you like for your calendar table (e.g. weekday numer, calendar week, month, year, etc.) and have that in one formula it's really easy to just copy that formula and paste it into a new report and adjust the start/end dates accordingly. If you use the New Column method then you'll have to re-do all of those steps again which could be quite onerous.
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |