How about a FIRSTDATE()?

October 9, 2014

Guest Post by Willem van Dijk

Intro

I am, like most (if not all) visitors to this website, an addict for information –> data –> truth –> Εμπειρία <-hope that sounds like Greek to you…

My journey into Power Pivot has been one of uphill struggles and downhill thrills. It’s been brief moments of ego feeding, immediately followed by lengthy bouts of meekness. One moment I am Indiana Jones going for his gun in The Raiders of the Lost Ark directly followed by the same scene in The Temple of Doom


Where did my DAX go?

Read the rest of this entry »


Blending “Time of Day” Analysis with Calendar/Date Analysis

October 7, 2014

Post by Rob Collie
 
Blending “Time of Day” Analysis with Calendar/Date Analysis in Power Pivot and Power BI

Our “Morning” Website Traffic is Down 21.5% in Jan 2014 vs. Jan 2013, But “Evening”
Traffic is Up by a Similar Amount, and Full-Day Traffic is “Flat” at +0.9%

(Fake Data, But Real Analysis)

Two Different Flavors of “Time”

Usually, when we talk about “time” in Power Pivot, we’re talking about the Calendar/Date flavor:  How much have things changed from yesterday to today.  What are our Month to Date numbers, and how do those compare against the same period last year?  Let’s call this “macro-trending.”

But time of day is also often interesting:  what are the trends WITHIN a day?  Let’s call this “micro-trending.”

And then, the hybrid of the two:  how are our “micro” trends changing over the course of the year, month, quarter, etc?

I don’t think the techniques here are terribly complicated, but they might be a little difficult to conjure up on your own.  So, it’s time for a post – and a downloadable workbook! Smile

The Key:  Separate the Date and Time Components!

Read the rest of this entry »


Compare product performance after launch

June 26, 2014

By Avichal Singh

As part of the microsoft.com/learning team we release products throughout the year –courseware, books, exams and eLearning (check out MVA for tons of free courses). We often need to understand how our products perform after launch and how they compare against each other (in the first month since launch, first quarter, first year etc.). For Example: we would compare the various courses we launched around Visual Studio 2010. We may compare Visual Studio 2010 against Visual Studio 2012 courses. We may even compare Visual Studio against SQL Server. Or compare adoption by geography or customer segments.

I can imagine similar need for other businesses, e.g. a car manufacturer who needs to compare performance of various year, make and models.

Power Pivot and Power View can allow us to go from View 1 below, which is inscrutable at best, to View 2 which really helps us understand and differentiate the adoption ramp of various products. In this article, I would explain how you can go from View 1 to View 2 using the car manufacturer example.

View 1: Monthly Sales by Car Model
Typical view available in BI, but not very insightful

Power View Graph Monthly Sales by Car Model

 

View 2: Cumulative Sales since Launch, by Car Model
Clear view into adoption ramp of various products

Power View Graph Cumulative Sales since launch by Car Model

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 »