DAX Power BI: Creating a calendar with additional parameters
Supplement your calendar, which consists of only one column, to see additional insights.
Create additional columns, for example, year number and month number using the YEAR and MONTH functions
To create a month name, let’s use the FORMAT function
Use the FORMAT function to create a column with months. This function outputs the month from the date in a certain format. The name of the month can be changed depending on the number of letters “MMM”
This column is useful for sorting the data by month. Without this column, the months will be displayed in alphabetical order.
Now we can create the [Quarter] column. We will write it through the IF function.
Let’s complete our table by creating another column. Let’s create a decade and a week number.
Let’s start by creating a decade using the IF and DAY functions.
The week number can be created in a similar way, but the month is cut into 4 parts instead of 3. But here you need to consider the definition of the beginning of the week. It is wrong to consider that a week, for example, starts on the first day of each month, and so on.
Let’s create a column with week numbers. There is a special function WEEKNUM for this purpose. In this function, we define from which day the week starts — Monday or Sunday.
A two here means that the week starts on Monday.
Now let’s define the day of the week and use the FORMAT function to calculate it.
Here, as with the month, when displaying the result in the report, the days of the week will be sorted alphabetically, which is not suitable for us. To fix this problem, let’s create a column with the numbers of days of the week. We use the WEEKDAY function, which returns a value from 1 to 7.
The two has the same meaning here as in the previous example.
Now, based on the day of the week number, you can create a column with a status for (either it is a workday or a weekend).
Let’s create another column with year and date, for example 2021–01. To do this, first create a column with the month
And let’s do a concatenation operation
If we wish, we can create the table in an alternative way by creating the table in DAX code using ADDCOLUMNS
Among other things, we can add our own columns that we need in the data model. Also, as we can see, the calendar is created based on the minimum and maximum year in the Orders table.
Don’t forget to subscribe to
and join our Power BI community
