Do You Use Dynamics NAV (Navision?)

October 16, 2013

 
I’ve been asked by a reader to see who else is out there that is using MS Dynamics Navision, and either a) already using Power Pivot to analyze that data  or b) are considering it.

If you or your organization fit that description, drop me a note at this address:

ideas@powerpivotpro.com

In the mail, please answer the following brief questions:

  1. Which Dynamics NAV Version are you using?
  2. Does your version run on SQL Server or the “native” data store?
  3. What does your organization “track” in NAV?  (Sales, purchases, etc.)

No one is going to try to sell you anything, nor will I publish/share your information.  I’ll provide more details when I hear from you.


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 »


Why PowerPivot is Great For Accountants (Guest Post by Robert Stamsnijder)

July 2, 2013

 
Intro from Rob:  Robert Stamsnijder is one of those people who’s been weaving in and out of my life for awhile.  I’ve had Skype conversations with him in probably four different calendar years.  He surfaces, delivers a visionary sermon with intensity and color, and then disappears below the radar, working fervently on his master plan(s).  Then he resurfaces and reports that project was merely Phase One, and he’s now moved on to Phase Three.  A very interesting, intelligent, and entertaining gentleman.  And I am happy to welcome his first guest post here on the blog.

 

<- by Robert Stamsnijder, aka The Mean Dutchman Smile

After reading Rob’s blog What is PowerPivot? (back in October 2009) the thought came to my mind that PowerPivot would have a gigantic impact on the accounting profession. Why? Well, if one looks at the long, long list of accounting scandals one would think that accountants will surely welcome every kind of technology that can assist them in improving the quality of their work.

There are 2 kinds of accountants

Read the rest of this entry »


Adding a Minimum Threshold Slicer to “Stores That went negative” Technique

April 30, 2013

 
Thursday’s Post “Fixed” The Number of Negative Stores for a Month at 8.  Now We Vary That Threshold That With a Slicer.  PowerPivot is Amazing :)

Thursday’s Post “Fixed” The Number of Negative Stores for a Month at 8.
Now We Vary That Threshold That With a Slicer.

Let’s take Thursday’s post and extend it a bit.

In the picture above you’ll see that I have 5 selected as my threshold on the new slicer, and 48 months “qualify” for that threshold – there are 48 months where at least 5 stores were negative.

Now let me select 9 on the threshold slicer:

Raising the Threshold to 9 Weeds Out 10 More Months, Only 38 Months Exhibited 9+ Negative Stores.  Did i mention that PowerPivot Rocks? :)

Raising the Threshold to 9 Weeds Out 10 More Months, Only 38 Months Exhibited 9+ Negative Stores

How’d I Do This?

Read the rest of this entry »


Showing Only Months/Weeks/Etc. When at Least N Stores Showed a Certain Behavior

April 25, 2013

 
image

Nice Pivot, But I Only Want to See Months Where Eight
or More of My Stores Went Negative!

***Update:  Technique Extended, Workbook available

In a followup post I have added a slicer that lets the report user control the minimum number of stores required, rather than fixing it at 8 like this post does.  Also, the workbook is now available for download.

Find both in the followup post, located here.

Tales from Remote Consulting

Awhile back I left my job to start a new company.  I’m not yet ready to announce what that new company is about – I’m working hard on that and you folks will be the first to know.  Spoiler:  it’s about PowerPivot and Excel.

But in addition to hard work, there’s also a lot of waiting involved in all of that.  I’ve been filling the gaps with training and remote consulting to keep my head in the PowerPivot game.

Remote consulting in particular is a lot of fun – people send me a workbook, I spend 1-3 hours and build what they want, then send it back.  Gives me a good sampling of the problems that are “out there.”

One of those remote consulting jobs featured the problem pictured above (except that they had real data, and what I’m showing is 100% fake).

How Many Stores Fell Below Zero Each Month?

Read the rest of this entry »


Guest Post from Ken Puls: Determine Effective Tax Rate

February 7, 2013

Excel MVP Forever.  PowerPivot Pro On the Rise!

Back in December I wrote about Ken Puls’ role in inspiring the book, and described him as a DAX convert (and also someone who used to intimidate me, in a good way, at MVP Summits back when I was a newbie on the Excel team).  Well I’m happy to welcome a guest post from Ken today.

I think it’s particularly valuable to hear from a) someone who is still relatively new to the PowerPivot journey like Ken  and b) someone other than me, period – since both provide a very different perspective, and that helps us learn.

So, take it away Ken… Smile

Background

In British Columbia we’ve been working with a 12% HST (Harmonized Sales Tax) for the past 1.5 years. Effective April 1, 2013, we’ll be going back to a system with a separate 7% Provincial Sales Tax (PST) and our national 5% Goods and Services Tax (GST) instead. In our case, we wanted to look at sales that will not be PST taxable under the new tax structure, meaning that the effective tax on these sales will drop from 12% to 5%.

So assuming that we have the following tables in an Excel worksheet and the name of the tax table is tblTaxRates, it’s really easy to get the effective tax rate for any date:

tax-1

We simply add a VLOOKUP to the sales table with the following formula copied down the sales table:

=VLOOKUP([@Date],tblTaxRates[#All],2,TRUE)

Easy stuff for any Excel pro. But what do you do if your sales table is in PowerPivot, like this?

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 »


Modeling Viral Growth and Marketing in PowerPivot

January 18, 2013

A Tale of Two Charts

Let’s say you operate a business that relies heavily on “word of mouth” – customers recommending your product/service to their friends and colleagues. Or at least, you THINK it relies heavily on that sort of thing.

You need to decide how much to spend on traditional advertising – to supplement the social/viral marketing that your customers do on your behalf.  Take a look at each of these two charts – the captions for each attempt to capture the knee-jerk conclusions you might draw:

 
Modeling Viral Growth versus Traditional Direct Advertising in PowerPivot

“Advertising?  We Don’t Need No Stinking Advertising!
That is SO Yesterday!  We’re Viral Baby!”

Modeling Viral Growth versus Traditional Direct Advertising in PowerPivot

“All These Youngsters and Their ‘Viral This’ and ‘Social Media That’ – That’s All Just Fancy Excuses to Be Lazy – You Clearly Need to BRING Your Message to the Customer”

If chart 1 reflected reality, you may opt to spend very little on traditional advertising.  But in a chart 2 world, you’d be silly to rely on viral growth.  But which one (if either of them) describes your situation?

Back in October, Rahul Vohra (CEO of Rapportive) wrote a two-part blog series on this topic, posted here on LinkedIn.  I took a note, at the time, to revisit his work and “convert” it to PowerPivot.

It’s a very different kind of problem from what I normally do in PowerPivot – this isn’t about analyzing data I already have, but about calculating future outcomes based on a handful of parameters.  And that leads to some different kinds of thinking, as you will see.

 

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 »


New Customers per Day – Technique by David Hager

January 8, 2013

 
Hi folks.  Today we are fortunate to have a guest post from David Hager.  He explains a technique for counting how many new customers are acquired or “seen” each day.  (I’m going to think about whether this has web site traffic analysis uses as well – New Visitor vs. Returning Visitor sort of stuff).

***UPDATE:  Inspired by David’s work, I extended this technique to cover per Month, Year, Week, etc.:  http://www.powerpivotpro.com/2013/01/new-customers-per-day-generalized-to-new-customers-per-month-etc/

Count of New Customers per Day in PowerPivot

By David Hager

Information vital to any company is being able to identify customer patterns. Counting how many new customers per day a company acquires is perhaps the most important data that can be obtained. The following model will show how this can be done with DAX measures in PowerPivot. For comparison, two other measures are included in the Pivot Table (shown in Figure 1).

TotalCustomersPerDay:

=COUNTROWS(Table1)

Note that COUNT(Table1[CustomerID]) would return the same result.

DistinctCustomersPerDay:

=DISTINCTCOUNT(Table1[CustomerID])

This measure returns the number of unique customers.

NewCustomersPerDay:

=CALCULATE([DistinctCustomersPerDay],DATESBETWEEN(Table1[Date], BLANK(),LASTDATE(Table1[Date])), All(Table1[Date])) -
CALCULATE([DistinctCustomersPerDay],DATESBETWEEN(Table1[Date], BLANK(),LASTDATE(Table1[Date])-1), All(Table1[Date]))

This formula shows the real power of DAX. The first part of the formula (highlighted in green) returns the running total of the DistinctCustomersPerDay measure. The second part of the formula (highlighted in yellow) returns the running total of the DistinctCustomersPerDay measure up to the previous day of the pivot table row context. The difference affords the number of new customers per day.

 

Read the rest of this entry »


CFO Magazine Webcast on Monday

December 7, 2012

 
image

If You’re a CPA, and Need CPE Credit, Consider
Watching Me Show Off PowerPivot for an Hour

I’ve had an interesting new experience this week – I recorded a webcast for CFO Magazine.  Bill does these all the time and asked if I’d be interested in doing one this month. 

I’m usually game for this sort of thing, and it did turn out to be fun.  Squeezing a whirlwind tour of PowerPivot as well as a bunch of specific how-to techniques into a single hour – there’s a certain pace to that which I kinda liked.  I had to be thrifty with what I showed.

(I suspect Bill is a lot faster at recording these than I am however – I spent probably 20 hours recording a one hour webcast.)

Anyway, two key points:

  1. The webcast is not free.  It is certified as training for accountants and there is a $149 fee for the session, so unless you are a CPA, I suspect you are going to skip this one.
  2. It includes a copy of my book.  The $149 fee for the course does not go to me – that goes to the CFO Mag organization.  But you do get a copy of the book, and yes, they do pay me for the book.

What I Cover in the Webcast

Read the rest of this entry »