This chapter is from the book
This chapter is from the book
This chapter is from the book
Add Calculated Columns Using DAX
One downside to pivot tables created from PowerPivot data is that they cannot automatically group daily data up to years. Before building the pivot table, let's use the Data Analysis Expression (DAX) formula language to add a new calculated column to the Demo table.
Follow these steps to add a Year field to the Demo table:
- Click the Demo worksheet tab at the bottom of the PowerPivot Window.
- The column to the right of Revenue has a heading of Add Column. Click in the first cell of this blank column.
- Click the fx icon to the left of the formula bar. The Insert Function dialog appears with categories for All, Date & Time, Math & Trig, Statistical, Text, Logical, and Filter. Select Date & Time from the drop-down. You will instantly notice that this is not the same list of functions in Excel. Five of the first six functions that appear in the window are exotic and new (see Figure 2.17).
Figure 2.17 DAX offers a different list of functions than Excel.
- Luckily, some familiar old functions are in the list as well. Scroll down and select the YEAR function. Click the first date in the Date column. PowerPivot proposes a formula of =year(demo[Date]. Type the closing parentheses and press Enter. Excel will fill in the column with the year associated with the date, as shown in Figure 2.18.
Figure 2.18 A new calculated column is added. You will want to rename this.
- Right-click the column and select Rename Column. Type a name, such as Year.
- Repeat the process to add a Month column using a formula of =Month(demo[Date]). Figure 2.19 shows the table after adding two columns.
Figure 2.19 You now have years and months.
See Chapter 10, "Using DAX for Aggregate Functions" and Chapter 11, "Using DAX for Date Magic," for more information on DAX.
)
)
)
There are many more columns that you might think of adding, but let's move on to using the pivot table.