The GFITW “Loses” an ALL()

November 7, 2013

 

Yes, We’ve Seen This Image Before and I Am Sure We Will See it Again

Jump in the Wayback Machine…

In the Spring of 2011, I dove into a Power Pivot project that I thought was going to be simple, but even today remains the most complex thing I’ve ever done in DAX.  I think it’s fair to say that the experience, at the time, was traumatizing.  (The client’s business logic itself was/is incredibly complicated.  It’s 100% legitimate, but I think barometric pressure might be factored into their budget/actuals ratios.  Kidding.)

But like many difficult experiences, a lot of good came of it as well:

  1. I learned a ton – it forced me to advance my Power Pivot knowledge significantly
  2. It demonstrated to me that Power Pivot essentially had no “ceiling” – it could handle almost anything
  3. It became a Microsoft case study
  4. It “spawned” the GFITW.

Ah yes, the Greatest Formula in the World.  The solution to all our custom calendar needs, and a pattern I’ve repeated hundreds of times since.  On the blog, in the book, in client workbooks, everywhere.

Well it turns out, the GFITW could afford to go on a diet.

Um, Yeah.  The First ALL Isn’t Necessary (But Doesn’t Cause Problems)

Here’s the “classic” GFITW pattern:

Read the rest of this entry »


Traveling into the Future: Measuring Things that Lag

October 24, 2013

 
How do we compensate for "lagged" data in Power Pivot?

The Red Bars Are Accurately “Tied” to the Months in Which Those Sales Happened,
but the Blue Bars are Four Months “Late.”

Tales from Real Life

So you know, I wrote a book last year.  Being the author, I get reports on how well it’s selling.

What follows is strictly inevitable, considering the people involved and their addictive relationships with data.

Read the rest of this entry »


Simplifying Time Calculations and the User Experience using Disconnected Slicers

June 25, 2013

Guest Post by Jeff Lingen [LinkedIn]

How does PowerPivot fit in an enterprise BI environment

We don’t even know what it is yet. We don’t know what it is. We don’t know what it can be, we don’t know what it will be, we know that it is cool.

Zuckerberg’s early assessment of Facebook was a lot like how I felt after first discovering PowerPivot 3+ years ago. I knew it was cool but had no idea how it would fit into an enterprise business intelligence environment. For a long time PowerPivot for me was just a cool thing that I used for my own data analysis or for proto-typing tools that I would eventually turn into “enterprise-level” solutions. Today I need a pretty compelling reason not to use PowerPivot for almost all of my organization’s analytic requirements. So where does PowerPivot fit into the enterprise BI environment and how do you get associates engaged and use it to provide value?

Read the rest of this entry »

Counting “Active” Rows in a Time Period: Guest Post from Chris Campbell

April 9, 2013
  • image

  • “There’s a Fight Club up in Delaware City.”

  •     “Yeah, I heard.”

  • “There’s one in Penns Grove too.”

  •     “Bob even found one up in New Castle.”

  • “Did you start that one?”

  •     “No, I thought you did.”

 

 

It Keeps Spreading Smile

About a week ago I was talking to Chris Campbell and some of the other folks at Blue Granite.  Chris mentioned that he has been teaching some PowerPivot classes at the Microsoft Technology Centers, sometimes even in my neck of the woods, but I didn’t know until he told me.

Which, of course, instantly reminded me of the scene above in Fight Club.  I’m sure everyone else makes the same connection right? Smile with tongue out

Anyway, Chris asked if I would be interested in him writing a guest post and I said heck yeah!  So, without further delay, I give you the PowerPivotPro.com debut of Chris Campbell.

-Rob

The Problem

Recently, a customer sent me a question regarding a DAX problem they were working on. They have a Members table in their model that includes attributes of “Start Date” and “End Date” for each member. The question they needed to answer was “How many active members did we have in [fill in the blank]?” I thought this was a pretty interesting question and it seemed like it ought to be pretty easy to do in DAX.

Read the rest of this entry »


Guest Post: Always Show Yesterday, Today, or Tomorrow’s Data

March 28, 2013

 
Today we have a new guest poster – Miguel Escobar.  I’ve been talking to Miguel in email and Skype for a long time now and feel silly that I haven’t asked him to do a guest post until today.  But now, I have, so I can stop feeling silly.

From his writing style and creative approach to solving problems, I think you’ll see that he fits right in.

Cool trick: Always show Yesterday’s, Today’s or Tomorrow’s Data

clip_image002

Executive: Are these values correct?
Excel-guy: yes, but you need to check the dates slicers to see what dates the report is using
Executive: Ugh… I just want to click on the report and see the latest values

If you ever had this situation before let me tell you that you’re not alone on that one…I’ve been there before and it’s time to give you some cool easy tricks on how to set up a Powerpivot report that shows you the yesterday, todays, tomorrow, next week or any type of timeframe (forecasting or that sort of scenario).

Read the rest of this entry »


Modeling Viral and Marketing Growth, Part 3 of 3

January 24, 2013

Why am I doing this in PowerPivot?  Primarily as a challenge.

This is a question I should have answered before I even started down this road.

To be honest, I did it primarily as a challenge – to stretch my brain a little bit.  If I were faced with this exact same task in my daily work, undoubtedly I would just use normal Excel formulas.  In some ways, this modeling exercise has been a deliberate misuse of PowerPivot.  A handful of parameters with no source data whatsoever – this is NOT what the PowerPivot engine was built for, which explains why the PowerPivot solution is actually significantly more difficult than the Excel solution.

“So you’ve been deliberately wasting our time??”

No, I do think there is real value in this exercise, for two reasons:

  1. Brain-stretching with new techniques always comes in handy later.  For instance, on the first post Sergey commented that he’d been thinking about loan amortization measures and this could be applied to that.
  2. I can see this technique being added, as a supplement, to a broader PowerPivot model.  For instance, a model containing lots of real customer data over time, and then a [Projected Customers] measure that forecasts future customer populations based on various assumptions and/or marketing investments.

So with that in mind, here it is:  the final installment of viral/marketing modeling in PowerPivot.

Read the rest of this entry »


Modeling Viral and Marketing Growth, Part Two

January 22, 2013

 
Picking up from last week’s post, the first thing I want to show is that I kinda cheated last time.  To see what I mean, let’s look at Rahul’s original chart:

Viral Marketing Growth in PowerPivot:  Customers Flatten Out Over Time

In Rahul’s Viral Model, Total Customers “Goes Flat” Quickly

In Rahul’s model, if we start With 5,000 initial customers and a viral factor of 0.2, we end up with 6,250 customers and we never get any more!

But in my model from last week, if I use 5,000 and 0.2, customers keep piling up exponentially:

Exponential Ongoing Viral Growth in PowerPivot

In My Model from Last Week, Customers Never Go Flat –
They Just Keep Growing Exponentially

So why the difference?

Read the rest of this entry »


NETWORKDAYS() Equivalent in PowerPivot?

November 22, 2012

 
There is no NETWORKDAYS() Function in PowerPivot

There is no NETWORKDAYS() Function in PowerPivot

A Post on Thanksgiving?

Normally I would take today off and not have a post.  But I’ve posted so many updates about the book lately that it’s got to feel like this place has turned into an advertisement shop, and I want to keep the “real” content up.

So consider this a Thanksgiving “bonus” post – me giving thanks for everyone ordering the book, and everyone tolerating my desire to post updates about “my baby” every five minutes Smile

A Missing Function

It’s funny, I’ve never used NETWORKDAYS() much (if at all) in regular Excel, so I didn’t realize this until someone posted on the MrExcel forums – how do I do a NETWORKDAYS()-style calculated column?

Something like this:

NETWORKDAYS in PowerPivot

Desired Result

So how do we get to this?

Read the rest of this entry »


Last Mini-Post for the Day: Check out Chandoo’s PIVOT version of the Calendar Chart!

September 13, 2012

 
I thought I had seen everything.  I had not.  Chandoo, we bow before you:

Yes, That’s a PivotTable!  Click the Image to View the Post on Chandoo.org

(Oh, and I really need to figure out how to make these animated GIF’s.  Damn!)


PowerPivot Calendar Chart in Excel: Specific Steps for Adapting it to Work With YOUR Data

August 16, 2012

image

Modifying This to Work With Your Existing Workbook Isn’t Hard

Continuation

Given the continued popularity of the Calendar Chart and the post I did on its anatomy, I thought I’d continue today with a more pragmatic “how do I adapt this to work with my data?” post.

Adding the Calendar Chart to YOUR PowerPivot Workbook

OK, so you like the calendar chart but you don’t want to start from scratch in a new workbook?  You already HAVE a PowerPivot workbook and want to just “port” the calendar chart into THAT workbook?

It’s easy.  Probably a 30 minute task, and that includes the time spent reading this post.

Read the rest of this entry »


Explaining the PowerPivot Calendar Chart, Plus an Updated XLSX Download

August 14, 2012

 
image

Hidden Rows and Columns Visible, Color Coded, and Explained
(Slicers Deliberately Moved Aside for Clarity)
(Click for Larger Version)

A Most Popular Post Indeed!

Well the CalChart post was a hit – the second most popular post of this year in fact.  (Second only to Dan Battagin’s spreadsheet formatting post, and that one had the benefit of being directly linked to from the official Excel blog – Dan is a big cheater).

I particularly enjoy how many Excel Pros are arriving at this blog for the first time as a result of the CalChart – you know who you are!  You’re helpless against the luxuriant charms of the CalChart! :)

And you have to have PowerPivot for it to work, muhaha.  Resistance is futile.  Go download it from Microsoft now.  It’s free.

Modifying it to fit your needs

The workbook I made available for download last week included a bunch of unused “machinery” – formulas and cells that I created while I was experimenting with different techniques, but ended up not using in the final version.

Read the rest of this entry »


Introducing… the Calendar Chart!

August 9, 2012

 
Calendar Chart in Excel - PowerPivot Can Do Some Amazing Things

“CalChart” – The Most Absolutely Awesome Thing I’ve Ever Done in Excel
(Data:  Fake UFO Sightings/Alien Abductions – Data I 100% Made Up)

New Chart Type Added to Excel 2010!

Yes, it’s a new chart type.  And yes, it’s been added to Excel 2010.  But not by my former colleagues at Microsoft.  This was done by me, after being inspired by another Excel pro, and with a heavy dose of formatting and sparkline assistance from another.

And it’s not some new fancy software addin or something like that.

It’s formulas.  In the normal Excel grid.

Read the rest of this entry »