You are currently viewing How to use Date function in DAX Power BI?

How to use Date function in DAX Power BI?

In this post, we will learn how to use the Date function in DAX Power BI with different scenarios.

What is DAX in Power BI?

Data Analysis Expressions (DAX) is a formula expression language explicitly designed for the management of data models, the DAX formulas include functions and operators that can be used to create formulas and expressions in Analysis Services, Power BI, and Power Pivot in Excel.

DAX in Power BI includes a library of over 200 functions, operators, that are used by report designers to perform data analysis and calculations.

Date and Time DAX functions in Power BI

  • Power BI supports about 23 different types of Date & Time DAX functions, which allow you to create calculations based on dates and times, such as CALENDAR, CALENDARAUTO, DATE, DATEDIFF, DATEVALUE, DAY, EDATE, EOMONTH, HOUR, MINUTE, MONTH, NOW, QUARTER, SECOND, TIME, TIMEVALUE, TODAY, UTCNOW, UTCTODAY, WEEKDAY, WEEKNUM, YEAR, YEARFRAC.
  • Many of the functions in DAX are similar to the Excel date and time functions.
  • DAX functions use a datetime data type, and can take values from a column.

We covered before the CALENDAR and CALENDARAUTO functions in DAX. Now let’s begin to explain how to use the Date function in DAX Power BI.

Date function in DAX Power BI

  • The DAX DATE function generates the corresponding date of the input arguments in datetime format.
  • The DATE function is most useful in situations where the year, month, and day are supplied by formulas.
  • Date and datetime can also be in the format dt"YYYY-MM-DD"dt"YYYY-MM-DDThh:mm:ss", or dt"YYYY-MM-DD hh:mm:ss", for more details you can check format date function in dax


DATE(<year>, <month>, <day>)

DAX DATE Parameter


  • Can be a 1 to 4 digit number that represents the year but you should use the 4 digit to prevent unwanted results .
  • Dates beginning with March 1, 1900 are supported and If you entered a year between 0-1899, it will be added to 1900.
  • The function return #VALUE! error if the year is with a value of greater than 9999 or negative. 


  • A number from1 to 12. If you enter greater than 12 that represents the month,
  • If the month value greater than 12,it will evaluate it as a month of next year” the date is calculated by adding the value of month to the year“.


  • A number represent a Day from 1 to the last day in the given month.
  • Allowed from 1 to 31.
  • Negative integers are not supported,
  • Numbers greater than 31 will be treated as a day of the next month.

DAX DATE Examples

Example 1: In this example, we will show the result when passing the three arguments to the DATE function.

data1 = DATE(2022,3,19)
date and time dax functions in power bi

Example 2: If the value that you enter for the year argument is between 0 (zero) and 1899 (inclusive) then the value is added to 1900 to calculate the year.

Date inclusive = DATE(5,1,2)
Date function in DAX Power BI

Example 3: If you enter an integer in the month larger than 12, then the date is calculated by adding the value of the month to the year.

Date Format = DATE( 2021, 18, 1)
d3 | Power Platform Geeks

Example 4: If you enter a negative integer in the month, the months are subtracted from the beginning of the year.

Date Subtract = DATE( 2022, -6, 15)
d4 | Power Platform Geeks
negative month

Example 5: If the day is greater than the number of days in the month, then the day adds that number of days to the first day in the month.

Date Add = DATE(2021,1,35)
d5 | Power Platform Geeks
day>no month days

Example 6: If the day is less than 1, then the day subtracts the number of days plus one from the first day of the month provided.

Date Minus = DATE(2022,1,-16)
d6 | Power Platform Geeks

TODAY() VS NOW() in Power BI

Today and Now are Date functions that return the current date. but the main difference between the Today and Now Date functions is the NOW() returns the current Date and Time.

Today and Now in Power BI
Today and Now in Power BI


In Power BI the Dax Date function takes the integers as input arguments, and generates the corresponding date for these values. The DAX DATE function is most useful in situations where the year, month, and day are supplied by formulas.

See Also
Join us
Need Help
  • Have a related question? Please ask it at Community.

Mohamed El-Qassas

I'm Microsoft MVP, SharePoint StackExchange Moderator, Microsoft QnA Moderator, C# Corner MVP, Microsoft TechNet Wiki Judge, and Senior Technical Consultant with +12 years of experience in SharePoint, Project Server and Power Platform.