Open In App

Power BI – DAX Date Functions

Improve
Improve
Like Article
Like
Save
Share
Report

Date operation in Microsoft Excel is comparable to those in Data Analysis Expressions (DAX). However, the DateTime data types utilized by Microsoft SQL Server form the foundation of DAX functions. Microsoft Power BI uses the scripting language Data Analysis Expressions (DAX) to create calculated columns, measurements, and custom tables. It offers a number of useful functions that help create some very cool formulas and expressions that can be applied to a number of data analysis issues. 

DAX Date Functions

You can develop reliable and high-performance data models in Power BI by having a fundamental understanding of when and how to use the DAX functions. Your efforts to evaluate data will be more effective the more familiar you are with DAX functions. This is due to your ability to quickly access and analyze enormous volumes of data from several tables.

We need to load the data that contains the Date format field. Here we are taking the records of 50 products sold by a library supply company. Dataset Used: Sheet1

Loading-dataset

 

Then go to New Table Tab, which would contain the output column of the following quarries. 

new-table

 

DAX Calendar

Generates a table with a single “Date” column that is made up of a continuous range of dates. The range of dates is inclusive of those two dates and extends from the specified start date to the specified end date.

Syntax:

CALENDAR(<start_date>, <end_date>)

Example:

calendar = CALENDAR(05-01-1980,31-01-1990)

calendar-function

 

DAX Date

The supplied date is returned in DateTime format by the DATE function. It creates the corresponding date using the integers provided as parameters. When the year, month, and day are determined by formulas, the DATE function is most helpful. The underlying data, for instance, can include dates in a format that isn’t accepted as a date, such as YYYYMMDD. 

Syntax:

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

Example:

date = DATE(2013,11,18)

Date-function

 

DAX Datevalue

Translates a textual date into a date in DateTime format. The client computer’s locale and date/time settings are used by the DATEVALUE function to interpret the text value while executing the conversion.

Syntax:

DATEVALUE(date_text)

Example:

datevalue = DATEVALUE(“25 12 2002”)

date-value-function

 

DAX Datediff

Returns the number of boundaries between the time frame to compare dates with. Any of the following values may be the value of the Interval:

  • Second, 
  • Minute, 
  • Hour, 
  • Day, 
  • Week, 
  • Month, 
  • Quarter,
  • Year

Syntax:

DATEDIFF(<Date1>, <Date2>, <Interval>)

Example:

datediff = DATEDIFF(DATE (2001, 07, 01 ),DATE (2002, 12, 31 ),YEAR)

date-diff_year-function

 

Example:

datediff = DATEDIFF(DATE (2001, 07, 01),DATE (2002, 12, 31),MONTH)

date-diff_month-function

 

Example:

datediff = DATEDIFF(DATE (2001, 07, 01),DATE (2002, 12, 31),DAY)

date-diff_day-function

 

DAX Day

A number between 1 and 31 represents the day of the month. The date of the day you’re looking for is an argument for the DAY function. Dates can be passed to the method by inputting them in a DateTime format, using another date function, or using an expression that yields a date. Additionally, you can type a date in one of the acceptable date string formats.

Syntax:

DAY(<date>)

Example:

day = DAY(“25-12-2002”)

Day-function

 

DAX Edate

a date that is the specified number of months before or after the start date is returned. To determine maturity dates or due dates that occur on the same day of the month as the date of issue, use the EDATE function.

Syntax:

EDATE(<start_date>, <months>)

Example:

edate = EDATE(“28-11-1920”,4)

edate-fucntion

 

DAX Eomonth

Returns the date of the final day of the month, before or after a specified number of months, in DateTime format. To get maturity dates or due dates that fall on the last day of the month, use the EOMONTH function.

Syntax:

EOMONTH(<start_date>, <months>)

Example:

eomonth = EOMONTH(“28-11-1920”,3)

eomonth-function

 

DAX Month

The month is returned as a number ranging from 1 (January) to 12 (December). DAX works with dates in a datetime format as opposed to Microsoft Excel, which maintains dates as serial numbers. You can type a date in a supported datetime format, refer to a column that includes dates, or use an expression that returns a date to enter the date that will be used as the parameter to the MONTH function.

Syntax:

MONTH(<datetime>)

Example:

month = MONTH(“28-11-1920”)

month-function

 

DAX Now

A datetime format string that contains the current date and time. When you need to show the current date and time on a worksheet or compute a number based on the current date and time and have that value updated every time you view the worksheet, the NOW function comes in handy.

Syntax:

NOW()

Example:

now = NOW()

now-function

 

DAX Quarter

Gives back the quarter as a number between 1 (January through March) and 4. (October – December). The output value is also blank if the input value is blank.

Syntax:

QUARTER(<date>)

Example:

quarter = QUARTER(DATE(1990,02,14))

quarter-function

 

DAX Today

Gives the current date back. The TODAY function comes in handy when you want the current date to be shown on a worksheet regardless of when the workbook is opened. It can be used to compute intervals as well.

Syntax:

TODAY()

Example:

today = TODAY()

today-function

 

DAX Utcnow

Gives the current date and time in UTC. The UTCNOW function’s output only varies when the formula is updated. It isn’t always being updated.

Syntax:

UTCNOW()

Example:

utcnow = UTCNOW()

utcnow-function

 

DAX UtcToday

Gives the current date in UTC.

Syntax:

UTCTODAY()

Example:

utctoday = UTCTODAY()

utc-today-function

 

DAX Weekday

Gives a number between 1 and 7 indicating the date’s weekday. The day defaults to being from 1 (Sunday) to 7 (Saturday).

Syntax:

WEEKDAY(<date>, <return_type>)

Example:

weekday = WEEKDAY(DATE(2007,2,4))

weekday-function

 

DAX Weeknum

According to the return type value, returns the week number for the specified date. The week number shows the week’s numerical position within a year. For this purpose, two systems are employed:

  • System 1 – Week 1 is the year’s first week and corresponds to the week beginning January 1.
  • System 2 – The week that includes the first Thursday of the year is week 1 and is the first week of the year. The mechanism used in this system is described in ISO 8601, also referred to as the European week numbering scheme.

Syntax:

WEEKNUM(<date>[, <return_type>])

Example:

weeknum = WEEKNUM(DATE(2007,2,4))

weeknum-function

 

DAX Year

Provides a four-digit integer in the range of 1900 to 9999 as the year of a given date. The date in text or datetime format that includes the desired year.

Syntax:

YEAR(<date>)

Example:

year = YEAR(DATE(2022,3,12))

year-function

 

DAX YearFrac

Determines the fraction of the year that the number of full days between two dates corresponds to. To determine the percentage of benefits or responsibilities for an entire year to be assigned to a certain term, use the YEARFRAC spreadsheet function.

Syntax:

YEARFRAC(<start_date>, <end_date>, <basis>)

Example:

yearfrac = YEARFRAC(DATE(2022,3,12),DATE(2022,6,2),2)

yearfrac-function

 



Last Updated : 16 Jan, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads