Important Date and Time Functions in DAX (Power BI)

DAX provides a variety of date and time functions to analyze and manipulate date-related data. Below are some of the most commonly used ones, explained with examples using a Sample Superstore dataset.

1. TODAY()

Purpose: Returns the current date.
Example Use Case: Calculate the number of days since an order was placed.

Dataset:

Order ID	Order Date
1	2024-01-01
2	2024-03-15
3	2024-07-10

DAX Formula:

Days Since Order = DATEDIFF('Orders'[Order Date], TODAY(), DAY)

Output (assuming today is 2024-11-17):

Order ID	Order Date	Days Since Order
1	2024-01-01	321
2	2024-03-15	247
3	2024-07-10	130

2. YEAR()

Purpose: Extracts the year from a date.
Example Use Case: Create a column for the year of an order.

DAX Formula:

Order Year = YEAR('Orders'[Order Date])

Output:

Order ID	Order Date	Order Year
1	2024-01-01	2024
2	2024-03-15	2024
3	2024-07-10	2024

3. MONTH()

Purpose: Extracts the month from a date.
Example Use Case: Group orders by month.

DAX Formula:

Order Month = MONTH('Orders'[Order Date])

Output:

Order ID	Order Date	Order Month
1	2024-01-01	1
2	2024-03-15	3
3	2024-07-10	7

4. FORMAT()

Purpose: Converts a date to a specific format.
Example Use Case: Display dates in “Month-Year” format.

DAX Formula:

Formatted Date = FORMAT('Orders'[Order Date], "MMMM YYYY")

Output:

Order ID	Order Date	Formatted Date
1	2024-01-01	January 2024
2	2024-03-15	March 2024
3	2024-07-10	July 2024

5. WEEKDAY()

Purpose: Returns the day of the week as a number (1 = Sunday, 7 = Saturday).
Example Use Case: Determine if an order was placed on a weekend.

DAX Formula:

Order Day Number = WEEKDAY('Orders'[Order Date])

Output:

Order ID	Order Date	Order Day Number
1	2024-01-01	2 (Monday)
2	2024-03-15	6 (Friday)
3	2024-07-10	4 (Wednesday)

6. EOMONTH()

Purpose: Returns the last day of the month for a given date.
Example Use Case: Calculate the number of days left in the month.

DAX Formula:

End of Month = EOMONTH('Orders'[Order Date], 0)
Days Left = DATEDIFF('Orders'[Order Date], [End of Month], DAY)

Output:

Order ID	Order Date	End of Month	Days Left
1	2024-01-01	2024-01-31	30
2	2024-03-15	2024-03-31	16
3	2024-07-10	2024-07-31	21

7. DATEDIFF()

Purpose: Calculates the difference between two dates in days, months, or years.
Example Use Case: Calculate the age of a product since its launch.

Dataset:

Product ID	Launch Date
101	2020-05-10
102	2022-08-15
103	2023-11-01

DAX Formula:

Years Since Launch = DATEDIFF('Products'[Launch Date], TODAY(), YEAR)

Output:

Product ID	Launch Date	Years Since Launch
101	2020-05-10	4
102	2022-08-15	2
103	2023-11-01	0

8. NOW()

Purpose: Returns the current date and time.
Example Use Case: Calculate how many hours have passed since an order was placed.

Dataset:

Order ID	Order Date	Order Time
1	2024-11-17	08:00 AM
2	2024-11-17	02:00 PM

DAX Formula:

Hours Since Order = DATEDIFF(
    'Orders'[Order Date] + TIMEVALUE('Orders'[Order Time]),
    NOW(),
    HOUR
)

Summary of Key Functions

Function	Description
`TODAY()`	Returns the current date.
`YEAR()`, `MONTH()`	Extracts the year or month from a date.
`FORMAT()`	Formats a date into a custom text representation.
`WEEKDAY()`	Returns the day of the week as a number (1-7).
`EOMONTH()`	Returns the last day of the month for a given date.
`DATEDIFF()`	Calculates the difference between two dates in days, months, or years.
`NOW()`	Returns the current date and time.

These examples illustrate how DAX Date and Time functions can be applied to analyze and manipulate dates in Power BI effectively.

Learn Power BI from our experts with 10 Years of Data Analytics expertise for Multinational Companies : Click Here

Important Date and Time Functions in DAX (Power BI)