Extracting Date Components in Power BI Using DAX (Year, Month, Quarter, Day, and Weekday)
Introduction:
In Power BI Desktop, datasets often contain a Date column that stores complete date information. However, for proper analysis and dashboard creation, analysts usually need separate components of the date such as Year, Month, Quarter, Day, Day Name, Month Name, and Weekday.
Power BI allows us to create these values using DAX (Data Analysis Expressions) by adding a New Column in the dataset.
Step 1: Open Power BI Desktop
Open Power BI Desktop and load the dataset that contains the Date column.
Example dataset table name:
Tesla_dataset - Download Dataset
Date column:
Date
Step 2: Go to Data View
On the left side panel, click the Data View icon (table icon).
This view shows the dataset in tabular format where you can create and view calculated columns.
Step 3: Select the Dataset Table
From the Fields pane, select the table that contains the date column.
Example:
Tesla_dataset
Once the table is selected, Power BI activates the Table Tools tab on the top ribbon.
Step 4: Create a New Column
To create a calculated column:
-
Select the dataset table.
-
Go to the Table Tools tab.
-
Click New Column.
A formula bar will appear where you can write the DAX expression.
Step 5: Write the DAX Formula
In the formula bar, write the column name followed by the formula.
Example:
Year = YEAR(Tesla_dataset[Date])
Press Enter.
Power BI will create the column and fill the values automatically.
General Syntax of a New Column
Column Name = DAX Formula
Example:
Month = MONTH(Tesla_dataset[Date])
Creating Date-Based Columns Using DAX
1 Creating Year Column
DAX Formula
Year = YEAR(Tesla_dataset[Date])
Explanation
The YEAR() function extracts the year part from the date.
Example Output
| Date | Year |
|---|---|
| 10-03-2026 | 2026 |
2 Creating Month Number Column
DAX Formula
Month Number = MONTH(Tesla_dataset[Date])
Explanation
The MONTH() function returns the month number (1–12).
Example
| Date | Month Number |
|---|---|
| 10-03-2026 | 3 |
3 Creating Month Name Column
DAX Formula
Month Name = FORMAT(Tesla_dataset[Date], "MMMM")
Explanation
The FORMAT() function converts the date into text format.
The option "MMMM" tells Power BI to display the full month name.
Example
| Date | Month Name |
|---|---|
| 10-03-2026 | March |
Other format options include:
| Format | Output Example |
|---|---|
| "MMM" | Mar |
| "MMMM" | March |
| "MM" | 03 |
So the format option determines how the month will appear in the column.
4 Creating Quarter Column
DAX Formula
Quarter = "Q" & FORMAT(Tesla_dataset[Date], "Q")
Explanation
The FORMAT() function with "Q" returns the quarter number.
Example
| Date | Quarter |
|---|---|
| 10-03-2026 | Q1 |
Quarter classification:
| Quarter | Months |
|---|---|
| Q1 | Jan – Mar |
| Q2 | Apr – Jun |
| Q3 | Jul – Sep |
| Q4 | Oct – Dec |
5 Creating Day Column
DAX Formula
Day = DAY(Tesla_dataset[Date])
Explanation
The DAY() function extracts the day number from the date.
Example
| Date | Day |
|---|---|
| 10-03-2026 | 10 |
6 Creating Day Name Column
DAX Formula
Day Name = FORMAT(Tesla_dataset[Date], "dddd")
Explanation
The FORMAT() function converts the date into the name of the day.
The option "dddd" displays the full day name.
Example
| Date | Day Name |
|---|---|
| 10-03-2026 | Tuesday |
Other day format options include:
| Format | Output |
|---|---|
| "ddd" | Tue |
| "dddd" | Tuesday |
So the format parameter determines how the day information appears in the dataset.
7 Creating Weekday Number Column
DAX Formula
Week = WEEKDAY(Tesla_dataset[Date])
Explanation
The WEEKDAY() function returns the day of the week as a number.
Default numbering:
| Number | Day |
|---|---|
| 1 | Sunday |
| 2 | Monday |
| 3 | Tuesday |
| 4 | Wednesday |
| 5 | Thursday |
| 6 | Friday |
| 7 | Saturday |
Example
| Date | Week |
|---|---|
| 10-03-2026 | 3 |
Here 3 represents Tuesday.
Changing the Weekday Start
If Monday should be considered the first day of the week, use:
Week = WEEKDAY(Tesla_dataset[Date],2)
New numbering:
| Number | Day |
|---|---|
| 1 | Monday |
| 2 | Tuesday |
| 3 | Wednesday |
| 4 | Thursday |
| 5 | Friday |
| 6 | Saturday |
| 7 | Sunday |
Why FORMAT Options Are Important
Options like "MMMM" or "dddd" control how the information appears in the dataset.
For example:
| Function | Result |
|---|---|
| FORMAT(Date,"MM") | 03 |
| FORMAT(Date,"MMM") | Mar |
| FORMAT(Date,"MMMM") | March |
| FORMAT(Date,"ddd") | Tue |
| FORMAT(Date,"dddd") | Tuesday |
0 टिप्पण्या
कृपया तुमच्या प्रियजनांना लेख शेअर करा आणि तुमचा अभिप्राय जरूर नोंदवा. 🙏 🙏