Here are some commonly used Date and Time DAX functions along with their syntax and example-
1- TODAY DAX Function
Today() returns the current date.
Syntax: TODAY()
Example: Calculates the sum of sales amount for the current day.
TodaySales = CALCULATE(SUM(SalesTable[SalesAmount]), SalesTable[Date] = TODAY())
2- NOW() DAX Function
The NOW function in DAX is used to obtain the current date and time as a datetime value.
Syntax: NOW()
Example:
CurrentDateTime = NOW()
In this example, NOW() is called without any arguments. The function retrieves the current date and time when the calculation is executed.
3- YEAR DAX Function
YEAR(date) returns the year from the specified date.
Syntax: YEAR()
Example: Calculates the sum of sales amount for the year 2023.
SalesByYear = CALCULATE(SUM(SalesTable[SalesAmount]), YEAR(SalesTable[Date]) = 2023)
4- MONTH DAX Function
Month(date) returns the month from the specified date.
Syntax: MONTH()
Example: Calculates the sum of sales amount for the month of June.
SalesByMonth = CALCULATE(SUM(SalesTable[SalesAmount]), MONTH(SalesTable[Date]) = 6)
5- DAY DAX Function
DAY(date) returns the day from the specified date.
Syntax: DAY()
Example: Calculates the sum of sales amount for the 17th day of the month.
SalesByDay = CALCULATE(SUM(SalesTable[SalesAmount]), DAY(SalesTable[Date]) = 17)
6- EOMONTH DAX function
EOMONTH() returns the end of the month for the specified start_date.
It takes two arguments- the start_date and months. The start_date is the date for which we want to find the end of the month, and the months argument is optional and specifies the number of months to add or subtract from the start_date.
Syntax: EOMONTH(start_date, months)
Example: How to get the last day of the month for the specified date?
LastDayOfMonth = EOMONTH(DATE(2023, 6, 17), 0)
In this case, the start_date is DATE(2023, 6, 17), representing June 17, 2023. The months argument is set to 0, which means no additional months are added or subtracted.
In this example, the result will be the date June 30, 2023, as it is the last day of the month for June 17, 2023.
7- EDATE DAX Function
The EDATE function in DAX (Data Analysis Expressions) is used to calculate a new date by adding or subtracting a specified number of months from a given date.
Syntax: EDATE(start_date, months)
- start_date: The initial date from which the calculation begins.
- months: The number of months to add or subtract from the start_date. Positive values represent future dates, and negative values represent past dates.
Example 1:
Create a Measure with below code-
NewDate = EDATE(DATE(2023, 6, 5), 1)
So, the expression EDATE(DATE(2023, 6, 5), 1) returns the date July 5, 2023.
Example 2:
Add New column and write below dax code-
New-Date = EDATE('Sales'[OrderDate], 1)
In this example, 'Sales'[OrderDate] represents the column that contains the initial dates.
The EDATE function takes the "OrderDate" and adds 1 months to it. The resulting date will be stored in the "New-Date" column.
8- CALENDAR DAX Function
The CALENDAR function is used to create a calendar table that includes a continuous range of dates.
This function is commonly used to generate a table with a list of dates to support time-based calculations and analysis.
Syntax: CALENDAR(start_date, end_date)
Example: Generate a calendar table named “CalendarTable” that includes all the dates from January 1, 2023, to December 31, 2023.
CalendarTable = CALENDAR( DATE(2023, 1, 1), DATE(2023, 12, 31) )
You can further enhance the calendar table by adding additional columns such as year, month, day of the week, or other derived date-related information using DAX functions like YEAR(), MONTH() etc.
CalendarTable = ADDCOLUMNS( CALENDAR( DATE(2023, 1, 1), DATE(2023, 12, 31)), "Year", YEAR([Date]), "Month", MONTH([Date]) )
9- CALENDARAUTO DAX Function
Returns a table with a single column named “Date” that contains a contiguous set of dates. The range of dates is calculated automatically based on data in the model.
Syntax: CALENDARAUTO()
Example:
CalendarTable = CALENDARAUTO()
CALENDARAUTO() will scan the entire data model and identify a column with a date or datetime data type.
It will determine the minimum and maximum dates from that column and generate a calendar table covering the range of dates.
10- DATEDIFF DAX Function
The DATEDIFF function is used to calculate the difference between two dates in terms of a specified time unit.
It returns the number of time units (such as days, months, or years) between the start and end dates.
Syntax: DATEDIFF(start_date, end_date, time_unit)
Example:
DaysDifference = DATEDIFF(DATE(2023, 1, 1), DATE(2023, 12, 31), DAY)
In this example, the DATEDIFF function calculates the number of days between January 1, 2023 (start_date) and December 31, 2023 (end_date).
The time_unit parameter is set to "DAY", indicating that we want to measure the difference in terms of days.
Additionally, you can use other time_unit options such as "MONTH", "YEAR", "QUARTER" etc.
11- WEEKDAY DAX Function
The WEEKDAY function in DAX is used to determine the day of the week for a given date and return its corresponding numeric representation or name.
Syntax: WEEKDAY(date, [return_type])
- date: The date for which you want to determine the day of the week.
- Return type: 1, week begins on Sunday (1) and ends on Saturday (7). numbered 1 through 7.
Return type: 2, week begins on Monday (1) and ends on Sunday (7).
Return type: 3, week begins on Monday (0) and ends on Sunday (6).numbered 1 through 7.
Example:
DayOfWeek = WEEKDAY('Sales'[OrderDate], 1)
12- HOUR DAX Function
The HOUR function in DAX is used to extract the hour component from a datetime value.
Syntax: HOUR(datetime)
Example 1:
Create a New Column to dataset and write below dax-
OrderHour = HOUR('Sales'[OrderDateTime])
In this example, 'Sales'[OrderDateTime] represents the column that contains the datetime values. The HOUR function is applied to each datetime value, extracting the hour component.
Example 2:
Create a Measure and write below DAX code-
Hour("2023-06-17 15:30:00")