skip to Main Content

By Avichal Singh

As part of the 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

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.


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.

Power Pivot Data Model Diagram

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
Power View Graph need to align 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

Power Pivot Relationship

Results after Step 1
Line graphs are aligned at the same start point…but the month to month variability still makes it confusing
Power View Graph after Step 1

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 (),
        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
Clear view into adoption ramp of various products

Power View Graph 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.
Power View Graph Cumulative Sales since launch by Geography

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
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?

Power Pivot how to handle groups of products

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
– 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
Power Pivot how to handle groups of products

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

Avi Singh

Avi Singh has personally experienced the transformation and empowerment that Power BI can bring - going from an Excel user to building large scale Power BI solutions. His mission now is to share the knowledge about Power Pivot and Power BI.

This Post Has 6 Comments
  1. Very nice. I’ve been working up to this for some of our products, but hadn’t schedule the time to figure out the best way to do this. You’ve clarified my thinking, which will definitely shorten my learning curve. One particular product line we have is a three-year curriculum. All I’ve done so far is split out the years 1-2-3 in the columns and make rows on the years and months, which does show the expected trend of diminishing sales after launch. Graphing it this way would be much more precise. With this technique it would be easy to break that down further by teacher guide, student books, and supplements. There are also several grade levels, which could be compared in the same year and even quarter. Excellent post. Thanks!

  2. Our system does not even have a field for active date of a new product. I created a temp table of the first order date for each product and use that. For us, that is effectively the first availability date, since we always presell and always have orders on the first day. I use that rather than the first ship date, since some items can have hundreds of orders fulfilled when the first container or truck arrives, which would skew the launch numbers super high.

    At my previous employer it was the opposite. Many of our medical devices would have in-service or active dates set by engineering so that we could place units in the field for testing. It could be weeks or months before a product is approved for general sale. That green light comes from the FDA, so we had to have a separate date field set up that marketing would use to active the product for inclusion in our catalog, which was the date we were legally able to advertise it, etc. There would be orders fulfilled before that approval date, but they were done under research, filed test, or validation plans.

    Each organization needs to make a conscious decision what trigger to use and then formalize a process to set that date in stone.

  3. Hello – I am wondering how to stop one of the lines at the most recent data point even when the scale keeps going out? I have not been able to find out any information regarding that. Thanks – Ron Risden

    1. The line would stop when the Blank() value is returned (even when the scale keeps going out). You would have to write your DAX formulas to check for the Most Recent Data Point, compare and returns Blank() if you are past that date, say using an IF statement. IF(<condition to check if date is past the most recent data point>, <TRUE then return BLANK()>, <FALSE, then return calculated value>)

Leave a Reply

Your email address will not be published. Required fields are marked *