My theory for today’s post is to give a really serious look at a typical LTD measure, in hopes that it can reinforce our understand of filter context.  Let’s see how it goes LTD := CALCULATE([Sales], FILTER(ALL(Calendar), Calendar[Date] <= MAX(Calendar[Date])) We can see at left that indeed it works.  The $3,266,374 from 2001 plus the $596,747+$550,817 equals the $4,412,937 in the LTD measure, showing that it works at both the Year level, and the Month level… which is pretty cool. Before I Continue Reading

We start with two simple measures, that we have written like 900 times before: TotalSales:=SUM(SalesTable[Dollars])TotalSales-PY:=CALCULATE([Total Sales],SAMEPERIODLASTYEAR(Calendar[Date])) Drop those bad boys in a pivot table with Year on rows, and it looks HOT!    I briefly thought having a grand total was weird, since “Prior Year’ on a set of years feels slightly odd… but fine.  If you have { 2011, 2012 } then prior year would mean { 2010, 2011 }.  I can dig Continue Reading

I rarely use ALLSELECTED().  Last time I did, I had to go look at the MSDN docs, never a good sign.  But I have to say, unlike some of the docs… this one seems both accurate and understandable! I decided to play with it a bit today; here are the measures I tested with: Total Intensity:=SUM(Colors[Intensity])Total Intensity, ALL Level:=CALCULATE([Total Intensity], ALL(Colors[Level]))Total Intensity - ALLSELECTED Level:=CALCULATE([Total Intensity], ALLSELECTED(Colors[Level])) And here Continue Reading

As I referenced in my previous blog entry, we should take a look at DAX Table Queries.  In the world I live in, which is really focused on Power Pivot, you really don’t see these queries too often.  It probably doesn’t help that it’s a rather “hidden” feature in Excel.  I get the feeling it is more common to write these queries when doing work against a Tabular server, but that’s typically isn’t my space. Still, I think there are some interesting reasons to use them in Excel… if nothing else Continue Reading