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 |
|
|
|
View 2: Cumulative Sales since Launch, by Car Model |
 |
Note: The Rocket Ship, Hot Burner and Slow Burner analogy inspired by Tableau’s Tale of 100 visualization. It is interesting that we often do see our products cluster in three layers as shown, when we are analyzing our actual product performance.
Sections
Preview Data Set
Step 1: Create Dimension Table to show days since launch
Step 2: Create cumulative measure in fact table
Additional Considerations
Preview Data Set
We have the model as shown below. With the Sales fact table connected to the dimension tables: Cars, Date and Geography. This can generate the View 1 as shown above. Find a link to the workbook at the end of this post.
Step 1: Create Dimension Table to show days since launch
First problem we would solve is that products were launched at different points in time. We need to line them up, so for each, we can see the performance in Month 1 after launch, Month 2 after launch and so on.
| Need to align the start point of various product launch |
 |
Add DaysPastLaunch column to fact table
We add a new column in FactSales table as below (make sure to change data type to Whole Number), which gives us the days since launch.e.g. if [LaunchDate] for that specific Car is Jan 1st 2010 and PurchaseDate in FactSales table is Jan 2nd 2010, then DaysPastLaunch would show 1 day (past launch).
DaysPastLaunch =
[PurchaseDate] – Related(Cars[LaunchDate])
Create DaysPastLaunch table
Since we need flexibility in being able to analyze in month, quarter or years after launch we define a pseudo Date table with various attributes. The difference being, here each month is 30 days since we did not want the variability in number of days in a calendar month to affect our analysis. The final table looks as below.
| DaysPastLaunch a pseudo date table |
 |
Create relationship between fact and DaysPastLaunch table
Next create a relationship as shown
| Results after Step 1 Line graphs are aligned at the same start point…but the month to month variability still makes it confusing |
 |
Note: More on the (Blank) Year Month at the end of this post.
Step 2: Create cumulative measure in fact table
Instead of showing sales Quantity by Year Month past product launch, let us show the cumulative sales Quantity since the product launch. For that we create the formula as below. Based on the cumulative total dax pattern, slightly modified, to show blank when there is no sales Quantity.
SumCumulativeQuantity :=
IF (
ISBLANK ( [SumQuantity] ),
BLANK (),
CALCULATE (
[SumQuantity],
FILTER (
ALL ( DaysPastLaunch ),
DaysPastLaunch[DaysPastlaunch]
<= MAX ( DaysPastLaunch[DaysPastLaunch] )
)
)
)
Code beautified with DAX Formatter
That small change, from quantity to cumulative quantity, renders a beautiful graph, which lets us clearly see how our various products are performing.
|
Cumulative Sales since Launch, by Car Model |
 |
| You can also view adoption across other dimensions, like Geography or Customer segment Here we see that East and Central region show the best adoption for Alto car model. |
 |
Additional Considerations
There may be additional considerations which you would have to contend with. I touch upon them here, but would not fully cover them for the sake of brevity (laziness?).
Dealing with (Blank) DaysPastLaunch
You would notice in graph above that we have values where DaysPastLaunch field (Year Month past launch) shows (Blank). In the data set, we see transactions where the purchase date is prior to launch date. These transactions are not matched up successfully to the DaysPastLaunch table hence they show up under (Blank).
| Days Past Launch can be negative |
 |
Now either this is a:-
– Data issue and some data cleanup and validation is in order (perhaps Launch Date is earlier than as recorded)
– Valid scenario, perhaps involving preorders of a product. It may be best to extend the DaysPastLaunch dimension table to account for negative values as well.
Grouping products
In our contrived dataset here, we are contending with only Year 2010 models launched by our manufacturer. What if we had data for multiple car categories and year of manufacture?
 |
Note: You may notice that some Year 2010 models have a launch date in 2011 and 2012. Let us assume that these were 2010 models that were terribly delayed due to safety considerations.
How would we show comparison between groups of products. Say:-
– Sedan versus Coupe versus Hatchback
Or
– All ‘Year 2010’ Models versus all ‘Year 2011’ models
The Power Pivot model as built above, would actually work and show you the requisite graphs. However most likely you would want to build additional safeguards so data is used and interpreted correctly.
| Tread carefully when showing groups of products by DaysPastLaunch |
 |
Safeguard #1: Account for differing number of products
Different product groups may contain different number of products (e.g. perhaps we launched 10 Sedans but only 1 Hatchback car). You may address that by dividing sales Quantity by number of active products, thus getting sale Quantity per unit product.
Safeguard #2: Product group may have products with different timelines
E.g. the Sedan category may contain cars that were launched in 2010, 2011, 2012 and 2013. If you group these together you would get misleading results. Best way around is to add additional dimension which indicates the age of a product. Then filter down to products which are past a certain age, say only show products that are at least one year old.
Here is the link to the workbook: ProductPerformanceSinceLaunch.xlsx