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

DAX DATE Syntax

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

DAX DATE Parameter

Year 

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

Month 

• 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“.

Day 

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

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)

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)

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)

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)

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)

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.

Conclusion

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

• How to use Power BI DAX Bitwise Operations?
• How to use DAX Calendar function in Power BI?
• How to use Power BI DAX CALENDERAUTO?
• How to use Power BI DAX DAY Function?

Join us

• Subscribe to Power Platform Geeks.
• Register to Saudi Arabia Power Platform User Group.

Need Help

• Have a related question? Please ask it at deBUG.to Community.