Date And Time Functions (DAX) In Power BI - Part One

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:
 
Date and Time Functions (DAX) - Power BI
 
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())
 
Date and Time Functions (DAX) - Power BI
 
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
 
Date and Time Functions (DAX) - Power BI
 
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
 
Date and Time Functions (DAX) - Power BI
 
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.
 
Date and Time Functions (DAX) - Power BI
 
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
 
Date and Time Functions (DAX) - Power BI
 
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)
 
Date and Time Functions (DAX) - Power BI
 
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)
 
Date and Time Functions (DAX) - Power BI
 
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.
 
Date and Time Functions (DAX) - Power BI
 
TODAY
 
Returns the current date in datetime
 
Syntax- TODAY()
 
Ex- YEAR(TODAY())-2000will return 20.
 
Date and Time Functions (DAX) - Power BI
 

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.