Date And Time Functions (DAX) In Power BI

Introduction

In the previous article you can see the different types of DAX functions. I mentioned almost all DAX functions and their categories. So here in this article I will explain about ‘Date and Time Functions’ in Power BI and their syntaxes.

I mentioned 19 ‘Date & Time’ functions in my previous article so let's see what are these functions, their description and their DAX formula syntaxes.

Description

CALENDAR

Return a single column table, default column name is ‘Date’.

Syntax- CALENDAR(<StartDate>, <EndDate>)

<StartDate> and <EndDate> should be any DAX expression that returns Datetime value.

Ex- CALENDAR(DATE(2019,8,1), DATE(2020,3,1)).

Date and Time Functions (DAX) - Power BI

CALENDARAUTO

Returns a table with one column of dates calculated from the model automatically.

Syntax- CALENDARAUTO([FiscalYearEndMonth])

<FiscalYearEndMonth>Any DAX expression that returns an integer from 1 to 12. If you mention the number then it will start from that number to 12 by default. If omitted then it will start from minimum value to maximum value in calendar table of current user.

Ex- If min and max date of data model are May-01-2019 and June-30-2020 respectively then:

CALENDARAUTO()- returns all dates from Jan-01-2019 to Dec-31-2020

CALENDARAUTO(3)- returns dates from Mar-01-2019 to Feb-28-2021.

The below image represents the current user data:

YEAR

Returns a 4 digit year value from the date.

Syntax- YEAR(Date)

Date- A date of ‘datetime’ datatype or text

Ex- YEAR(“June 1990”) or YEAR(TODAY())

YEARFRAC

Returns the year fraction representing the number of whole days between start date and end date. IT returns a decimal number.

Syntax- YEARFRAC(StartDate,EndDate,[Basic])

[Basic]- (Optional) The type of day count basis to use. All arguments are truncated to integers. Limit- 0 to 4.

If Basic is <0 or >4 then it will return error.

Basic 0 - US (NASD) 30/360

Basic 1 - Actual/actual

Basic 2 - Actual/360

Basic 3 - Actual/365

Basic 4 - European 30/360

Month

Return a number from 1(January) to 12(December) representing the month.

Syntax- MONTH(Date)

Date- Date in ‘Datetime’ or ‘text’

Ex- MONTH(Salesperson(Hierdate)) or

MONTH(“June 19, 2020 1:45 am”)

In example 1, salesperson is a table name and hiredate is a column

EOMONTH

Returns the date of last day of the month before or after a specified number of months in datetime.

Syntax- EOMONTH(StartDate, Months)

StartDate- It should be in Datetimeformat.

Months- It is a number that represents the month, if the number is not an integer then it is rounded up or down to the nearest integer.

Ex- EOMONTH(“ August 15, 2020”, 1.5) will return August 31 2020 as months argument is rounded to 2.

QUARTER

Returns the number from 1(Jan-Mar) to 4(Oct-Nov) representing the quarter.

Syntax- QUARTER(Date)

Date- Should be date

Ex- QUARTER(DATE(2020, 3, 20)) returns 1

WEEKDAY

Returns integer number from 1-7 to show the day of the week

Syntax- WEEKDAY(Date,[ReturnType])

ReturnType- it is a number(1,2,3) that determines the return value

1 = Sunday(1) through Saturday(7)

2= Monday(1) through Sunday(7)

3= Monday(0) through Sunday(6)

Ex-WEEKDAY(salesperson(hiredate),1)

WEEKNUM

Returns the week number in the year. This number represents where the week falls in the year.

Syntax –WEEKNUM(Date, [ReturnType])

ReturnType- it is number(1 or 2). If it is 1 then week begins on Sunday or if it is 2 then week begins on Monday.

Ex-WEEKNUM(“Jan 19 2020”,2)

DAY

Returns a number from 1 to 31 representing the day of the month.

Syntax- DAY(Date)

Date- Date should be in datetimeformat.

Ex- DAY(“1-1-2020”) or DAY(“January 1 2020”) both formulas will return 1.

TODAY

Returns the current date in datetime

Syntax- TODAY()

Ex- YEAR(TODAY())-2000will return 20.

Summary

Hope you now understand the above explained DAX function. The rest of the ‘Date and Time functions’ will be explained in the next article. So wait for that. Thanks for reading.