Date and calendar functions in Power BI allow users to create dynamic and
insightful time-based columns. This blog will cover how to use key DAX
functions for working with dates, including FORMAT, EOMONTH, DATE, DATEADD, WEEKNUM, and WEEKDAY. We’ll also explore how these
columns enhance your data analysis.
1. Create a Year and Month Column with FORMAT
The FORMAT
function lets you extract and format date components like year and month from a
date column.
Syntax:
FORMAT(<value>, <format_string>)Example: To create separate Year and Month columns:
- Year
Column:
·Year = FORMAT(Sales[OrderDate], "YYYY")
- Month
Column:
·Month = FORMAT(Sales[OrderDate], "MMMM")
This generates readable columns like 2025 for Year and January for Month.
2. Create Last Date in Month Columns with EOMONTH
The EOMONTH
function returns the last date of the month for a given date.
Syntax:
EOMONTH(<start_date>, <months>)Example: To find the last date of the month:
Last Date in Month = EOMONTH(Sales[OrderDate], 0)This returns the last date of the month for each row in the OrderDate column.
3. Create the First Day of the Month with DATE
To find the first day of the month, combine the DATE and YEAR/MONTH functions.
Syntax:
DATE(<year>, <month>, <day>)Example: To create a column with the first date of the
month:
First Date in Month = DATE(YEAR(Sales[OrderDate]), MONTH(Sales[OrderDate]), 1)This returns the first date of the month for each OrderDate.
4. Create the First Day in the Last and Next Month with DATEADD
The DATEADD
function shifts dates by a specified number of intervals (e.g., months).
Syntax:
DATEADD(<dates>, <number_of_intervals>, <interval>)Example:
- First Day
of Last Month:
·First Day Last Month = DATEADD(DATE(YEAR(Sales[OrderDate]), MONTH(Sales[OrderDate]), 1), -1, MONTH)
- First Day
of Next Month:
·First Day Next Month = DATEADD(DATE(YEAR(Sales[OrderDate]), MONTH(Sales[OrderDate]), 1), 1, MONTH)
5. Comparing American and European Options with WEEKNUM
The WEEKNUM
function returns the week number for a date. By default, Power BI uses the
American system, where weeks start on Sunday, but you can switch to the
European system, where weeks start on Monday.
Syntax:
WEEKNUM(<date>, [return_type])Example:
- American
Week Number:
·Week Number (American) = WEEKNUM(Sales[OrderDate], 1)
- European
Week Number:
·Week Number (European) = WEEKNUM(Sales[OrderDate], 2)
6. Create Week Number Columns with WEEKNUM
To add a Week Number column:
Week Number = WEEKNUM(Sales[OrderDate])This creates a column with week numbers (e.g., 1 for the first week of the year).
7. Create the Last Date in a Week Column with WEEKDAY
The WEEKDAY
function determines the day of the week for a given date. Use it to calculate
the last date of a week.
Syntax:
WEEKDAY(<date>, [return_type])Example: To find the last date in a week (assuming weeks
end on Sunday):
Last Date in Week = Sales[OrderDate] + (7 - WEEKDAY(Sales[OrderDate], 1))8. Create the First Date in a Week Column with WEEKDAY
To find the first date in a week (assuming weeks start on Monday):
First Date in Week = Sales[OrderDate] - (WEEKDAY(Sales[OrderDate], 2) - 1)This returns the Monday of the week for each OrderDate.
9. Using Calendar Columns in Data Analysis
Creating date-related columns enables better time-based analysis in Power
BI. You can:
- Analyze trends: Compare
sales across months, weeks, or years.
- Filter data dynamically:
Use slicers with year, month, or week columns for interactive reporting.
- Create visuals: Visualize
data with time-series charts.
Example: If you create Year,
Month, and Week Number columns, you
can easily create a bar chart showing sales by week or a line chart comparing
month-over-month performance.