Introduction to Power Pivot - Microsoft Business Intelligence Tools for Excel Analysts - page 59

The bottom line is that you can't use all DAX functions with calculated columns. Because a calculated
column evaluates at the row level, only DAX functions that evaluate single data points can be used in
a calculated column.
A good rule is that if the function requires an array or a range of cells as an argument, then it's not
viable in a calculated column. So functions such as SUM, MIN, MAX, AVERAGE, and COUNT don't work
in calculated columns. Functions such as YEAR, MONTH, MID, LEFT, RIGHT, IF, and IFERROR that
require only single data point arguments are better suited for use in calculated columns.
Building DAX-driven calculated columns
To use a DAX function to enhance calculated columns, click the Invoice Header tab in the Ribbon.
Figure 3-17 contains an InvoiceDate column. Although this column is valuable in the raw table, the
individual dates aren't convenient when analyzing the data with a PivotTable. It would be beneficial
to have a column for Month and a column for Year. This way, you could aggregate and analyze data
by month and year.
Figure 3-17: Although this table has an InvoiceDate field, adding Year and Month columns would allow for
better time-based analysis.
For this endeavor, you use the year() , month() , and format() DAX functions to add time
dimensions to your data model. Follow these steps:
1. In the InvoiceHeader table, click in the first blank cell in the Add Column column on the far
right.
2. In the formula bar, type =YEAR([InvoiceDate]) and press Enter.
Power Pivot automatically names the column CalculatedColumn1.
3. Double-click the CalculatedColumn1 column label and rename the column Year .