As referenced in my previous post, the numbers I was getting for memory usage from Kasper’s VBA Script did not make sense to me. After taking the time to better understand how Power Pivot stores data… it became clear to me why. Let’s look at what the script does, and does not, include… and figure out how much we care. Continue Reading
How does Power Pivot store and compress data?
Intro When I start writing a blog article, I don’t exactly know what to expect. Though… this is basically true in everyday conversation with me, as well. It’s hard to know what words are about to fly out of my mouth. It’s scary for everyone involved. That said… I expect this article to be kind of… epic. Equal parts technical, totally made up, and totally useful As mentioned in this previous post on performance gotchas, … well, many things… but mostly that Power Pivot performance issues Continue Reading
Lifetime To Date – Deep Dive
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
Bug in SAMEPERIODLASTYEAR()?
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