# New DAX functions in SQL Server 2016

By: Daniel Calbimonte | Comments | Related: > Analysis Services Development

##### Problem

There are new features for Data Analysis Expressions (DAX) in SQL Server 2016 and in this tip we will look at a few of these new DAX functions.

##### Solution

SQL Server 2016 comes with several new features including new DAX functions to enrich the functionality. In this tip, we will show some of these new DAX functions.

## Requirements

- SQL Server 2016 installed with a Tabular Server installed.
- The Adventureworks Tabular solution installed.
- I assume that you already have experience in DAX. If not, I recommend you to read this tip first, introduction to DAX.

In order to start, open SSMS and connect to a Tabular Server.

## DAX Calendar Function

Let's start with the CALENDAR function. This new function returns a column with the consecutive dates from a start date until a specified end date. The following example will show all the days between January 1, 2015 and January 31, 2015.

evaluate

(

CALENDAR (DATE (2015, 1, 1), DATE (2015, 1, 31))

)

The result displayed by the DAX query is the following:

## DAX CALENDARAUTO Function

Another new statement is CALENDARAUTO. This statement returns a set of dates in a column based on the data in the model.

evaluate

(

CALENDARAUTO ()

)

The statement will return the dates of the Adventureworks tabular model starting on January 1, 1916 until December
31, 2014.

You can specify a parameter in the CALENDARAUTO with values from 1 to 12:

evaluate

(

CALENDARAUTO (6)

)

If you specify a value of 6, you will get a set of consecutive dates starting on July 1, 1915 (6 months added to the earliest date) to June 30, 2015 (6 months added to the maximum date).

## DAX PI Function

Another new function is PI, which is the mathematical constant that represents the ratio of a circumference in relation to its diameter. This value is approximately: 3.14159. Let's call this function using DAX:

evaluate

(

ROW

(

"PI",PI()

)

)

The result returned would be the following:

## DAX SIN, COS and TAN Functions

We also have new trigonometric functions like sin, cos and tan. In a right triangle, there are fixed relationships between the Hypotenuse, Opposite and the Adjacent:

The formulas to calculate these values are as follows (if you do not remember your school classes):

Sin A = a/c

Cos A = b/c

Tan A = a/b

Let's use this function to calculate the cos of 45 radians:

evaluate

(

ROW

(

"COS",COS(45)

)

)

The result will be the following:

As you can see, the trigonometric functions are in radians. If you need to work in degrees there are 2 options:

Option 1 is to convert the degrees to radians by multiplying the radians by PI/180:

evaluate

(

ROW

(

"COS IN DEGREES, METHOD 1",COS(45*PI()/180)

)

)

The result in degrees is the following:

The other method is to convert the radians to degrees using the RADIANS function (which is also new in SQL Server 2016):

evaluate

(

ROW

(

"COS IN DEGREES METHOD 2",COS(RADIANS(45))

)

)

You can for example calculate sin^2(x) + cos^2(x)1:

evaluate

(

ROW

(

"sin^2+cos^2",(power(sin(45),2)+power(cos(45),2))

)

)

The result of the formula is 1:

## DAX MEDIAN Function

There are other functions like the MEDIAN, which is the middle value of a list of values or the mean of two middle values if there is no single middle value. In DAX, we can use this example:

evaluate(

summarize(

'Internet Sales',

[ProductKey],

"Total Sales", MEDIAN('Internet Sales'[Sales Amount] )

)

)

The example shows the MEDIAN of the internet sales group by ProductKey:

## Conclusions

In this tip, we covered some of the new functions incorporated in DAX for SQL Server 2016. We displayed some samples of the new functions. I hope you enjoyed this tip.

##### Next Steps

- There are several new functions in DAX, for a complete review this link.
- Check out these tips on DAX:

##### About the author

**View all my tips**