Introduction to power pivot data analysis expressions (DAX) part 2
In this first article, we looked at a relatively simple use of DAX using the Microsoft ‘Northwind’ data which for years has been the sample data provided with Microsoft Access.
Northwind includes a set of sales invoice data. This data includes the amount of each invoice line and the name of the salesperson responsible for the sale as well as the order date relating to each invoice. We are eventually going to create a PivotTable that will compare the sales for each year with sales for the previous year and then work out a budget.
Having got the background of DAX out of the way in part 1, we will now consider the use of the CALCULATE() DAX expression in practice. One of the things that you can’t do in PowerPivot is to group existing fields within the PivotTable itself – this is a particularly useful technique for working with dates, when you might want to report on sales by month or by year, but your dates are recorded day by day. Here, to overcome this shortcoming of PowerPivot, we have used the DAX YEAR() and MONTH() functions to add Year and Month columns to our Northwind invoice data:
Continue reading
This content is not freely available. To access 'Introduction to power pivot data analysis expressions (DAX) part 2 ' you need to be one of the following:
-
ACA student
-
This content is available to ACA students. If you want to start the ACA qualification there are several routes you can take
-
Excel community
-
Unlock the power of Excel and reduce your risks with practical guidance and support to improve your spreadsheet skills.
-
ICAEW member
-
Gain access to world-leading information resources, guidance and local networks. 98% of the best global brands rely on ICAEW chartered accountants.