Download DAX Formulas for PowerPivot PDF

TitleDAX Formulas for PowerPivot
File Size11.1 MB
Total Pages255
Table of Contents
First Chapter
Copyright page
Supporting Workbooks and Data Sets
A Note on Hyperlinks
1- A Revolution Built on YOU
2- What Version of PowerPivot Should You Use?
3- Learning PowerPivot “The Excel Way”
4- Loading Data Into PowerPivot
5- Intro to Calculated Columns
6- Introduction to DAX Measures
7- The “Golden Rules” of DAX Measures
8- CALCULATE() – Your New Favorite Function
9- ALL() – The “Remove a Filter” Function
10- Thinking in Multiple Tables
11- “Intermission” – Taking Stock of Your New Powers
12- Disconnected Tables
13- Introducing the FILTER() Function, and Disconnected Tables Continued
14- Introduction to Time Intelligence
15- IF(), SWITCH(), BLANK(), and Other Conditional Fun
16- SUMX() and Other X (“Iterator”) Functions
17- Multiple Data Tables
18- Time Intelligence with Custom Calendars:  Advanced Use of FILTER()
19- Performance:  How to keep things running fast.
20- Advanced Calculated Columns
21- The Final Transformation:  One Click That Will Change Your Life Forever
A1- Further Proof That the Game is Changing
A2- So Much Power, So Little Space:  Further Capabilities
A3- Four Common Error Messages
A4- People:  The Most Powerful Feature of PowerPivot
Document Text Contents
Page 2

DAX Formulas for PowerPivot


Rob Collie

Holy Macro! Books
PO Box 82

Uniontown, OH 44685

Page 127

118 DAX Formulas for PowerPivot: A Simple Guide to the Excel Revolution

Figure 192 New measure shows us a running total of YTD sales for each month!

And like all good PowerPivot measures, this formula is “portable” into basically any report shape you desire,
just by rearranging the pivot – no formula surgery required! Remove [Total Sales] and drag Year to Columns…

Figure 193 Our new [Total Sales YTD] measure, like all good DAX measures, automatically adjusts to any new pivot
shape – just rearrange using the field list, and the measure does the hard work!

Anatomy of DATESYTD()
Function Definition

DATESYTD(<date column in calendar table>, <optional year end date>)

That first argument, <date column in calendar table>, is common to nearly all of the time intelligence func-
tions. In PowerPivot itself, the function help just refers to it as Dates:

Figure 194 What I call “<date column in calendar table>, PowerPivot calls “Dates” – whenever you see that, remember
my version of it, because that’s what “Dates” means in the time intelligence function definitions.

DATESYTD() is used as a <filter> argument to CALCULATE(), much like ALL() and FILTER().

Page 128

11914- Introduction to Time Intelligence

How Does it Work?
Like almost everything else “magical” in PowerPivot, DATESYTD() operates by manipulating filter context.
Let’s return to a simple pivot layout, and highlight a particular measure cell:

Figure 195 For the highlighted measure cell…

DATESYTD() essentially identifies the latest date in the current filter context, and then “expands” the filter
context backward from that date to the first date of the year (more specifically, to the first date in the year
of that previously-identified latest date, which is 2004 in this case).
OK, then DATESYTD() modifies that filter context. Here’s how.

Figure 196 If we imagine the Calen-
dar table as a calendar rather than
a table, where each row in Calendar
is a single date, these are the active
dates (rows) in the filter context for
the measure cell highlighted in the
prior figure.

Again, visualizing the Calendar ta-
ble in calendar form:

Figure 197 DATESYTD() starts at the
last date in the existing filter context,
and then “expands” the filter context
back to the first date of the year (the
first date in the year of the current fil-
ter context)

Resulting in a new filter context:

Figure 198 New filter context high-
lighted (again visualizing the Calen-
dar table as a calendar)

Page 254


Page 255

246 DAX Formulas for PowerPivot: A Simple Guide to the Excel Revolution

Coming in Fall 2013
Simple, Impactful Techniques for the Data Professional
Bill Jelen and Rob Collie join forces to bring you dozens of magical, immediately useful and easily-applied
techniques in a single reference. PowerPivot alchemy is a fast-paced guide to more than 50 techniques you
can easily copy and apply to your work in minutes.
The results, however, often exceed what a team of programmers could accomplish in a month. Whether you
let your colleagues in on your secret weapons is entirely up to you.

Similer Documents