A common accounting task is to consolidate the financial results of subsidiary companies into combined results of a parent company. A parent company can have one or multiple subsidiaries. Each subsidiary can also have one or multiple subsidiaries beneath it.…
Figure 1 View outside of my home office window May 2nd, 2018 Fairbanks, Alaska. Intro Hello, my name is Matthew Brice and I am guest broadcasting from my home office (via tape delay) in Fairbanks, Alaska. PPPro put out a…
The Journey Begins I am an accountant fascinated with data. My PowerPivot journey started in 2013 when I encountered the PowerPivotPro blog while googling to find a way to tackle a multi-dimensional financial reporting task. Because of the limitations of…
Intro How did I come to PowerPivot and the related tools? Desperation in trying to fix a process problem. How did I get to the point where I can return a reasonable answer to a data query using the Power…
A few years ago, on February 14th, 2013, I fell head over heels onto a rocket ship that kicks just as much, well it kicks more ass than Tyler Durden on his best day. Valentine’s Day coincidence? Maybe I’ll get…
Your First Co-Development Project P3 has tried many different flavors of our brand of “consulting” through the years, but co-development projects that combine real projects with coaching and knowledge transfer have turned out to have been the sweet spot in…
Welcome to the latest PowerPivotPro Coffee Talk, where members of the community discuss various topics related to Power BI, Power Pivot, and Analytics/BI in general. These conversations take place during the week on a Slack channel, and are then lightly…
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://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).
Note that COUNT(Table1[CustomerID]) would return the same result.
This measure returns the number of unique customers.
=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.
***UPDATE – FULL: Wave one filled up fast, no need for 48 hours. We actually went over 100 during the night and hit about 130. We’re letting all of those in, but are taking down the signup form now.…
***UPDATE – FULL: Wave one filled up fast, no need for 48 hours. We actually went over 100 during the night and hit about 130. We’re letting all of those in, but are taking down the signup form now.
Stay tuned for news about Wave Two
If Ebenezer Scrooge Were Alive Today, He’d Use PowerPivot. And He’d Love This Post.
(Believe it or not this is an original image I commissioned ten years ago)
Taking my “High Priest” Role VERY Seriously
There are a few themes that I just keep hammering on, month after month. Most of those revolve around the stunning new future opening up for Excel Pros. I believe every thing I say about that stuff. It is REAL.
One of the biggest and most transformational changes is this: taking your workbooks to the web. Workbooks were being emailed around back when Roxette topped the worldwide music charts. PowerPivot workbooks published to a server are a very, VERY different experience, one that inspires MUCH more respect from the person consuming them.
Short Version: Free Forever for Lighter Workloads
This week, Pivotstream is launching something that I’ve been dreaming about for a long time: a way for you (yes YOU, dear reader) to harness the power of PowerPivot server (publishing workbooks as interactive web apps)… for free.
Not a trial. This is more of a Dropbox-style model where lighter usage is completely free, and you only pay if you want more capacity. I want to remove any barriers I can so that you can experience what I’m talking about (without bankrupting my company of course), and I think we’ve figured out how to do that. But before I get into details, let me show you something.
Just Add Upload
Thanks to a recent focus group I recruited here on the blog, I learned that many people expect there to be some sort of intensive conversion process – it seems like you would need to put a lot of work into a workbook before it becomes an interactive web application like the ones on our demo site.
So I recorded a video showing that it’s much, MUCH simpler than that. Just upload
Upload and Share – Short Video Illustrating an Even Shorter Process
(I recommend watching in HD and Fullscreen)
Benefits to Excel Pro
I didn’t have time to cover this in such a short video, so here’s a quick table comparing the old way to the new way, through the eyes of you, the Excel Pro: