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)).
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.