Power BI - DAX Date Functions

---

---

You need to import the "D:\employee_department.xlsx" dataset before implementing the Date functions in the BI desktop. These functions are very helpful for building and interpreting the complex data model. You may select either the new measure option or new table/new column options to write the expression and execute them.

How to Implement the Date DAX Functions?

Richard is trying to execute the date function on the BI desktop. He just loaded the dataset "Employee_department".xlsx. He selected the "Table Tools" tab and clicked "New column" presented in the Calculations group. The new column will append to the existing table and enter the expression in the formula bar.

d1= Date(2024,09,24) 

And then press Enter.

As he can visualize in the screenshot, the date is inserted in the new column d1.

The Date format could also change. Select the "Column tools", click on the "Format" tile, and choose the desired format like Short Date from the drop-down menu. The designated date format will be reflected in the d1 column.

HOUR Function

It will extract the hour from the specified date and time whose values vary from (0 to 23).

Example − Enter the following expression −

emp_hr = HOUR("February 22,2024 7:00 pm")

Here, a valid date and time are written inside the Hour function. The resulting value is 19. As the time is 7 pm which means it will calculate hours like (12+7).

WEEKDAY Function

The numeric value representing the week's day on the specified date, which ranges from one (Sunday) to seven (Saturday), will be obtained by the weekday function.

For example −

Id_day = WEEKDAY("march 04, 2024")

As Richard can notice in the screenshot, the result is 2 which means Monday falls on this specified date.

WEEKNUM Function

It will retrieve a numeric value that indicates the number of a week. You can specify the designated date as the first parameter inside the WEEKNUM function and the second parameter is optional that identify the week commencing from which day. The calendar convention will be employed in this function.

For example −

week_nm = WEEKNUM("September 01,2010")

NOW Function

As time varies from country to country. Richard can identify the current time and today's date through the Now() function. He selected the "New measure" from "Table tools".

Example − Enter the given expression −

current_time = Now()

As he can notice in the screenshot, todays date along with the present time is shown.

EOMONTH Function

Is there any function for bill payments or identifying the project or report submission deadlines?

Yes, with the assistance of the EOMONTH function, Richard can swiftly repay their bills like mobile phone bills, loan payments, EMI, and so on, and extract the last day of the month later or earlier according to the designated month which can be specified in this function.

Example − Enter the expression in the formula bar −

bill_py = EOMONTH("November 12,2023",2)

Here bill_py indicates the new measure. Enter the correct date format initially inside the EOMONTH function and 2 represents the next two months that is January from the mentioned date. It will return the last date of the January month. If the second parameter is negative like -2, then it will extract the last date just before the mentioned date.

YEAR Function

It extracts the year of the specified date. The range of the year starts from 1900 and ends till 9999.

Example − Enter the formula −

yt = YEAR("Mar 01, 2010")

Here, yt is the name of the new measure, and the year from the given parameter is extracted.

YEARFRAC Function

It comprises two parameters which specify the earlier date and finishing date. The year of fraction between these dates can be determined by this function.

Example − Enter the expression −

yt = YEARFRAC("September 23,2007","November 23, 2008")

CALENDER Function

If you want to enter the sequential dates between two specified dates, so in this case Calendar function is employed. You may choose the "Table tools" tab and select the "New table" from the "Calculations" group.

Example − Consider the given expression −

cal = CALENDAR("march 10,2024","March 30,2024") 

As you can observe in the screenshot, the sequential dates between these two designated dates are inserted in the new table.

DATEVALUE Function

It permits users to transform the date which is in textual form into a valid DateTime pattern.

Example − Enter the expression −

d_value = DATEVALUE("11 02 2010")

After implying this function, the textual form "11 02 2010" will be converted into the proper format as shown in the screenshot.

DATEDIFF Function

It will compute the interval between two designated dates. The interval values may be Year, Month, Week, Day, Hour, Quarter, Second, or Minute.

For example −

d_value = DATEDIFF(DATE (2020, 06, 20 ),DATE (2020, 11, 27 ),MINUTE)

Another Example −

Consider the given expression where year interval is used.

d_value = DATEDIFF(DATE (2020, 06, 20 ),DATE (2021, 11, 27 ),YEAR)