Part 1: Power Bi Data Model – calendar table
Hello!
I thought of starting this series of articles about Power Data models in a different way. If you are beginning in Data Model Power BI is important to know this theme calendar table.
If you desire to follow this article with the archive get into our repository GitHub.
Firstly, there are many reasons to start creating models in Power BI with a date table. But, is the exact reason clear to you?
Secondly, in Power BI, there is only one allowed relationship connection between two tables for the primary column, which is commonly referred to as Key, ID, etc.
Well, knowing this we can understand the importance of this table. Therefore, numerous tables need to be connected by date fields. These calendar tables allow us to have numerous relationships between these tables.
There are many methods for creating a date table:
- Dax function
- M function
Thirdly, and most importantly in calendar table Power BI. When it comes to data modelling, a fundamental premise is optimizing the model. Therefore, when creating a calendar table, this premise should be followed. We can analyze two creation strategies for these tables:
- Data interval. Here, we can define a date range to optimize the table’s size and create faster queries as a result.
- Dynamic calendar table. Personally, the option that appeals to me the most is having a table that is exactly the size needed for data relationships. As the date range increases, the table dynamically increases in size.
By closely analyzing the advantages and disadvantages of each data table creation strategy.
| Calendar table – data interval | Dynamic calendar table. | |
| Advantages | We will have a rationalized model for an analysis period. | It guarantees coverage for historical data analysis up to the present moment. |
| Disadvantages | The operator needs to pay attention to the interval period. | Depending on its partition, it may have an excessive size for your needs, causing slowness in the analyses. |
In conclusion, it is a simple, but important phase to follow to have a better structure in Data Model.
Well, let’s work with these options. For instance, I prefer using more Dax functions
First of all, chose the intended period, in our case, it comes to be between 2011-05-31 to 2022-12-31
- DAX Function:
- Dates = CALENDAR(DATE(2011, 5, 31), DATE(2022, 12, 31))
- CALENDARAUTO([compo de data da tabela Fato ])
- M Function:
- = List.Dates(#date(2011,05,31), 365*10, #duration(1,0,0,0))
Moreover, all of these functions will return a table, initially with a single column with the date value of the model range.
Second, In Power BI Desktop, go to the table on the ribbon. Select New Table in data view mode and then enter the following DAX function:
After, for M function the path must be in the tab of Data query, Home, New Source and finally select the Balck Query option.
Enlarging the table
From that moment we can customize our date table to have greater granularity. Let’s create the following columns: year, month number, week of the year, and day of the week. Concluding this, select New Column on the ribbon and insert the following DAX equation, which retrieves the year from the Date table.
Year = YEAR(Dates[Date])
Do the same process to retrieve the month number, week number, and day of the week:
- DayoftheWeek = FORMAT(Dates[Date], “DDDD”)
- MonthNum = MONTH(Dates[Date])
- WeekNum = WEEKNUM(Dates[Date])
Using M Function
= List.Dates(#date(2011,05,31), 365*10, #duration(1,0,0,0))
Pay Attention!!!
Don’t forget to convert it to a table.
Is simple and very important.
Go to Data View display and clique with the right button in this new table. After, choosing the option mark it as Data Table and finish.
See more in our article lists:
– Power BI and other topics
– If you want to learn more about this topic click on this Microsoft link
