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 »

1 Comment | Accounting/Financial Techniques, CRM, Custom Calendars, DAX, Marketing Analysis, Quick Tips & Tricks, Time Intelligence | Tagged: , , | Permalink
Posted by powerpivotpro

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 »

3 Comments | DAX, Quick Tips & Tricks, Time Intelligence | Permalink
Posted by powerpivotpro

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 »

11 Comments | Accounting/Financial Techniques, CRM, DAX, Quick Tips & Tricks, Time Intelligence, Web Analytics | Permalink
Posted by powerpivotpro

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 »

5 Comments | Accounting/Financial Techniques, Custom Calendars, DAX, Quick Tips & Tricks, Time Intelligence | Permalink
Posted by powerpivotpro

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 »

14 Comments | DAX, Time Intelligence | Tagged: , , , , | Permalink
Posted by David Churchward

Same Store Sales Continued – Using Store Open/Close Dates

August 23, 2012

 
In a comment on Tuesday’s post, Cory asked the following question:

“I want to use the stores start date to determine if it should be included in the same store calculation or not. Y/Y SSS calculation regardless if there are sales for that store this year or last for the specific date range I’m looking at (typically by month for last 13 months). The other twist to my SSS calculation is the store must be open for at least 15 months before it is included in my SSS Total. I hope my question makes sense. Thanks for any guidance you or anyone can offer.”

Remember that in my first post, if a store had sales last year, and this year, I included it in the SSS (Same Store Sales) calc.

If we have columns in our data indicating the date a store opened (and when it closed), it’s a very rational thing to want to use that instead.

So I invented some new columns on my fictional Stores table:

I'm Going to Use These (Fictional) Store Open and Close Dates to Drive my PowerPivot Same Store Sales Calc

I’m Going to Use These (Fictional) Store Open and Close Dates
to Drive my PowerPivot Same Store Sales Calc

So, how do we do that?

First, I create a measure to calculate when a store opened.  I add this to the Stores table itself:

Read the rest of this entry »

8 Comments | Accounting/Financial Techniques, DAX, Quick Tips & Tricks, Time Intelligence | Tagged: | Permalink
Posted by powerpivotpro

Calculating “Same Store” Sales in PowerPivot

August 21, 2012

 

Calculating Same Store Sales in PowerPivot

Another one from the forums

I love the PowerPivot forum over at Mr. Excel for many reasons, one of which is that it gives me useful, real-world inspiration for blog posts.  Recently, I saw someone asking about how to calculate “same store sales.”

Simply put, same store sales means comparing sales of ONLY stores that are open today and were also open at the same time last year (or last month, last quarter, etc.).

In other words, it’s a “year over year” (or month over month, etc.) comparison measure that only looks at stores that were open last year, and are still open today.

Read the rest of this entry »

2 Comments | Accounting/Financial Techniques, DAX, Quick Tips & Tricks, Time Intelligence | Tagged: | Permalink
Posted by powerpivotpro

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 »

Leave a Comment » | Cube Formulas, Custom Calendars, Quick Tips & Tricks, Slicers, Time Intelligence, Usability and Appearance | Permalink
Posted by powerpivotpro

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 »

6 Comments | Cube Formulas, Custom Calendars, Quick Tips & Tricks, Time Intelligence, Usability and Appearance | Permalink
Posted by powerpivotpro

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 »

30 Comments | Custom Calendars, PivotCharts, Quick Tips & Tricks, Slicers, Time Intelligence, Usability and Appearance | Permalink
Posted by powerpivotpro

Creating a Cycle Plot PivotChart

July 31, 2012

Guest Post by Colin Banfield [LinkedIn]

A cycle chart is a chart designed to show business sales cycles in a manner that cannot be expressed using other standard charts. For instance, a cycle charts can show how monthly sales vary over several years, or how daily sales vary over several weeks. For a good introduction to cycle charts, see this excellent article by Naomi Robbins.

Figure 1 shows cycle PivotCharts using 3M+ rows of sales data from the Contoso database.

 

image

Figure 1 – Monthly sales over years and daily sales over weeks cycle plots (click figure to see an expanded image)

Read the rest of this entry »

1 Comment | Accounting/Financial Techniques, Custom Calendars, DAX, PivotCharts, Quick Tips & Tricks, Time Intelligence, Usability and Appearance | Permalink
Posted by ColinBanfield

Excel 5-Calendar Date Table

May 17, 2012

Guest Post by Colin Banfield [LinkedIn]

image

For some time, I have been looking around for a fairly complete date table in Excel for use with PowerPivot. If you are working with data derived from a data warehouse, a date table is perhaps the most common dimension table that exists in the warehouse. However, not every scenario involves working with a data warehouse directly, and I simply wanted a “portable” date table. I found very little online, the best perhaps being this Excel table offered by the Kimball group (the table has been expanded since I originally downloaded it). I could have modified the Kimball table for my particular needs, but I decided to create one from scratch.  Late last year, Rob posted an article titled the Ultimate Date Table, which is available from the Azure Marketplace. I considered using this table instead of the one I was building in Excel, but the “Ultimate Table” lacks fiscal periods. Much of the analysis work I do includes fiscal periods.

Read the rest of this entry »

11 Comments | Custom Calendars, Data Import, Quick Tips & Tricks, Time Intelligence | Permalink
Posted by ColinBanfield

« Previous Entries