Dear Accountants: PowerPivot is your friend!

Guest Post by: Mike Griffin

Intro by Avi: Power Pivot and Power BI tools can be used to transform BI for a wide array of industry verticals and vocations. But it is especially suitable to some roles; Accountants are probably at the top of that list! Our friend Mike here is a Financial Manager in the interesting vertical of Cruise Lines. And has a post for us describing just one of the ways they are using the Power BI tools, in this case to find needles in the haystack. Take it away Mike…

Accountants are NOT typically data GEEKS

Accounting related problems open doors to a different set of applications for PowerPivot and PowerQuery. Although it’s fair to say most accountants like numbers, an affinity for numbers does not always translate into a love for data – especially lots of data. This example illustrates how PowerPivot and PowerQuery can be used to help automate accounting related tasks that can be incredibly time consuming when a lot of data is involved.

The scenario I’m presenting is not sophisticated in terms of DAX formulas and is very simple from a data modeling point of view. However, it’s an incredibly useful application of the tools we use as PowerPivot enthusiasts that can save valuable time when closing the accounting period.

The Accounting Need: Remove needles from the hay stack

Use Power Pivot and Power BI to look for the proverbial needles in your data haystack
Use Power Pivot and Power BI to look for the proverbial needles in your data haystack

In this scenario, I need to reverse invoice specific journal entries that were originally posted as part of an automated process between an internal database and our accounting software. This entry is posted as a batch with thousands of other invoices (the original journal entry can’t just be reversed).

Read the Rest

Moving Averages, Sums, Etc.

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

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

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

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

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

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


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