The Ultimate Date Table–Revisited

February 27, 2014

 
Guest post by Dominik Petri, who I am stoked to say is translating the book into German!  Today he shares a cool “hybrid” technique that blends two of my favorites things – Data Market and Power Query!

Take it away, Dominik…

Selecting a DateStream calendar from Power Query Navigator taskpane

 

As you might already know from Rob’s “The Ultimate Date Table” post, you need a separate calendar table to unleash the power of Power Pivot’s time intelligence functions. You want one for free? Fully customizable? Running up to a variable end date? Updated automatically every time you open your workbook? The time has come…

Get a great calendar table – for free

Boyan Penev has put together various calendar tables that you can download from Azure DataMarket directly into Power Pivot – for free. Again, see Rob’s post for details. So far, so good. But it is a pain to filter the data range you want to import. But there is Power Query!

Read the rest of this entry »


Defanging the “contiguous date selections” error

January 16, 2014

Calculation Aborted.  Function DATEAD only works with contiguous date selections.  Power Pivot, why you gotta be so cryptic?

Have You Ever Seen This Cryptic Error?  Here’s a Quick Explanation and Fix Smile

So you’ve got a DATEADD measure…

Let’s say you’ve written a nifty measure called [Last Year Sales] that returns the sales value you had one year ago at the same time:

  [Last Year Sales] =

  CALCULATE([Total Sales, DATEADD(Calendar[Date], –1, Year))

And hey, that works great:

image

So far so good:  [Last Year Sales] Measure is Working Great!

Read the rest of this entry »


A Simple Trick for Combating “Stale” Slicers

December 3, 2013

 
Help!  My Power Pivot Slicers Don't Auto Update to the Latest Date on Refresh!

Two Weeks of Refreshes Later…  the Report Still Thinks
Nov 15 is What Everyone Wants to See First!

Your Dashboard is Refreshed, But its Slicers are Stuck in “Yesteryear”

This is a trick I’ve been using forever but never blogged about.  Enough of you are now using Power Pivot for SharePoint (PP Server) that its time has come.  And really, it’s relevant on the desktop too.

On the day you first made this report, you selected the most recent Date (or Week, etc.) in the slicer.  And you saved the report.  All was right with the world!

But then, tomorrow comes.  And all of your slicers still have that “old” date selected, even after you refresh everything.  Ick.  Who wants to go and update all of those slicers to point to the latest date?

I sure don’t.  So, like me, you just let them sit on an old date (or Week, Month, etc.)  This forces the consumers of that report to ALWAYS click the latest date, sometimes after scrolling the slicer to the bottom.  Every time they open the report.  They.  Don’t.  Like.  That.  And neither would you.

Read the rest of this entry »


Cumulative Interest or Inflation, Multiplying Every Value in a Column, Why Don’t We Have PRODUCTX?

November 4, 2013

 
Cumulative Inflation/Interest in Power Pivot - Who Needs PRODUCTX?

Given the First Two Columns at Left, How Do We Calculate the Two Columns at Right?
(It’s 1.14 * 1.18 * 1.08 * 1.08…  How do we do that?)

I Take No Credit for the Question Or the Answer!

So much coolness and none of it is mine.  Today I am merely a messenger.

First of all a great question came in on an old blog post (the one about Moore’s Law and Inflation):

HOW do you perform cumulative inflation calculations in Powerpivot. What seems trivial, multiplying all the values in a column together has me stuck, as there is no PRODUCT() formula. The only way i can think of doing it is through messy iterations but that can’t be it.

Awesome question Andy!  And I was stumped.  How do we multiply every number in a column together, which is what we need to do here?  I mean, if we had a PRODUCTX() function I’d know where to begin.

To the Mother Ship (Microsoft) We Go!

I think the world needs more X functions (I’m serious, that’s not a joke, although it IS a funny thing to say, so I say it often). 

So I took this opportunity to ask my former colleagues at Microsoft – can we have a PRODUCTX function in the future?  And is there a way to “fake it” in the meantime?  Spoiler alert:  the answer is yes.

Jeffrey Wang is One Smart Dude

Read the rest of this entry »


A Simple Measure for Projections with Act! CRM

August 27, 2013

Guest Post by Vivek Gargav

Intro from Rob:  continuing the series of guest posts that got temporarily shelved, today we have one from Vivek Gargav.  This post captures a trend that I monitor quite closely:  people who are heavy users of ERP, CRM, and Accounting systems are increasingly realizing that Power Pivot provides FAR superior reporting and analysis, against those systems, than what those systems provide “in the box.”

It makes perfect sense of course.  If you are a software company who produces CRM software, your specialty is…  CRM software!  Not reporting and analysis software.  Furthermore, the needs of your customers couldn’t be more divergent – whatever you build “in the box” is inevitably going to be “lowest common denominator” stuff – borderline relevant to everyone, but not terribly insightful for anyone.

Enter Power Pivot.  And people like Vivek Gargav.

Background

By Vivek Gargav (twitter | website)

Act! CRM:  Begging for some Power Pivot Analysis

ACT! – a Leading CRM System.

Act! is one of the leading Contact Management/CRM products used by many SMBs worldwide within organisations that in some cases don’t have a formal IT department or resource internally. For many, the sole point for reporting on their organisational knowledge is via the weak and complex native Act! reporter, some even use the Dashboard component to extend their reporting capabilities.

Unfortunately neither of these routes provide the depth of reporting requirements that most businesses need (especially aggregate data) and so Act! users traditionally have had to look at 3rd party add-ons including the old stalwart of Crystal Reports.

Read the rest of this entry »


Moving Averages, Sums, Etc.

July 30, 2013

 
Moving Average in Power Pivot

The Blue Line Smooths Out Random Fluctuations, Tells a Less “Over-Reactive” Trend

I realized recently that this topic has never been covered before, in its most straightforward form, on this site!  Actually, it was the subject of a guest post by the esteemed David Churchward, and also by the equally-esteemed Kasper de Jonge, but neither of those posts benefited from the v2 functions available to us today).

To illustrate what we can do with state-of-the-art Power Pivot formulas, let’s start with this simple model:

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 »

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 »


Calendar Tables: Not Just for Formulas. Use Them on Your Pivots Too!

January 15, 2013

 
Year to Date (YTD) PowerPivot Measure (Calculated Field in 2013) is Returning Blanks and Prior Month (Previous Month, Last Month, etc.) is returning the same results as the original Sales measure.

The Pivot On the Left is Correct and the Pivot on the Right is Not.
But the Formulas Are the Same in BOTH Pivots!

A Semi-Common Question

From time to time, someone asks me a question of the following flavor:

“I’ve followed all of your advice on writing a ‘Year to Date’ Sales measure.  I’ve got a Calendar table and my DATESYTD function uses the Date column from that table.  But I am getting blank values for my YTD Sales measure and my Prior Month Sales measure is incorrect too.”

It Might Not Be Your Formulas!

In both pivots above, the formulas are identical:

[Last Month Sales]=
CALCULATE([Total Sales], DATEADD(Calendar[Date], -1, Month))

[Total Sales YTD]=
CALCULATE([Total Sales], DATESYTD(Calendar[Date]))

So what’s the problem in that pivot on the right?

Read the rest of this entry »


New Customers Per Day Generalized to “New Customers per Month,” etc.

January 10, 2013

 
 
A Generalized New Customers (or unique visitors) in Time Period - per Month, Year, Etc. in PowerPivot

A Generalized “New Customers in Time Period” Solution, Inspired by Tuesday’s Post

David Hager’s post on Tuesday really planted a seed in my brain.  And then a comment on that post from Charlie got me thinking further.

How can we extend the “New Customers per Day” concept to become “New Customers in <Any Period of Time>?”  New Customers per Month for instance.

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 »


Gantt Chart in PowerPivot

August 28, 2012

Guest post by David Churchward [Twitter]

Gantt Chart in PowerPivot

Gantt Chart in PowerPivot, fully dynamic and sliceable!

It’s a rare diversion from the normal Financial stuff that I subject you to!  Having run numerous projects and found myself writing endless task lists in Excel so that I can distribute to other people, it suddenly occurred to me that PowerPivot can do this.

Not only can PowerPivot do Gantt Charts, I think it destroys other software in this game.  Sure, MS Project is always going to be there and I’m very fond of it, but, anyone familiar with this:

“Thanks for sending that through, but I don’t have MS Project.  Can you give me an Excel version or PDF”?”

Maybe you haven’t, but I find myself continuously confronted with distributing Excel task lists and producing my own Gantt charts in Excel to grab screenshots for presentations.  PowerPivot gives me (and you) the means to monitor and update tasks quickly and easily, but it also gives you the means to distribute via Sharepoint – awesome!

If you want to just get on with it, GRAB THE FILE HERE and start using it.  There’s some brief instructions on how to use it in the file.  Otherwise, read on …

Read the rest of this entry »