Tonight I was looking at one of my recent posts, the one about “fuzzy” time relationships in calculated columns, and I discovered that I had used the EARLIER() function in places that I did not need to.

PART 1 and PART 2 of this series on Profit and Loss posts covered the basic layout of the P&L together with some time intelligence and filtering to display relevant numbers to cover actual, budget and prior year for both a selected period and the equivalent year to date.  This was all based around the core measure referred to as Cascade_Value_All.

Intro from Rob: today, Colin finishes in Percentile Measures topic from last week.

Note that 1) this technique requires the new release of PowerPivot that is currently still in beta (the Denali release)…

 
A long time ago I did a post on using slicer selections in Excel formulas.  That technique only worked when you select single values on slicers, though – any more than one and the dreaded “(Multiple items)” foils your well-laid plans.

PowerPivot provides a host of great functions like DATESYTD, DATESMTD, DATESBETWEEN, etc. that are useful for calculating many things, including a running total. But with the exception of DATESBETWEEN,

“I will start with the calculate curry. And a green tea.”
-Wise first time DAX customer
Is There a Preferred Way To Learn DAX Measures?

UPDATE: My book, which explains the PowerPivot formula language (DAX), in down-to-earth fashion tailored to the Excel audience, releases November 6, 2012. I wrote it to fill the “DAX for Excel people” gap that existed between all of the previous books. People have been asking me seemingly forever to do this, I finally got around to it.

My book, which explains the PowerPivot formula language (DAX), in down-to-earth fashion tailored to the Excel audience, releases November 6, 2012. I wrote it to fill the “DAX for Excel people” gap that existed between all of the previous books. People have been asking me seemingly forever to do this, I finally got around to it.