PowerPivot DAX case: compare product sales by months since product introduction

A few weeks ago I got this interesting question from someone. He wants to compare the sales growth of his products in the months since the introduction of the product. Lets say “Product One” first selling date is 1/2/2010 and “Product Two’s” first selling date is 2/1/2010. If we want to compare the sales since introduction we want to compare month 0 between the two products. Month 0 for Product One is 1/2/2010, for Product Two it is 2/1/2010.

I created a table with some data to simulate this:

As you can see different products have different start dates. The first thing we need to do is enrich our table with a new column that will determine the amount of months between the date from the current row and the date of the first sale for this product. Is we have that month we can use this in our report to compare months, first month of sales for each product will be 0, the next month 1 and so forth. This makes comparisons very easy.

Let’s get started with getting the first date for each product. So what do we have to here ? For each sales transaction in the table we need to check all the other sales transaction from the same product to determine the first salesdate. We can do this with a Calculate column using the MINX function:

=MINX(
FILTER(Sales,Sales[Productname]=EARLIER(Sales[Productname]))
,Sales[SalesDate])

So what does this formula do ? Give me the MIN value of Sales[SalesDate] for all the rows in the Sales table where the Sales[Productname] is the same value as the Sales[Productname] from the current row.

The function to note here is EARLIER(). What does this do ? well FILTER(Sales) gives us a filter over the entire Sales table, we no longer are aware of the current row of the table we are in, the earlier goes to the previous row context, being the current row of the table we are running the formula of the calculate column in.

This results in the following value for each row:

As you can see we now have the first date for each product available. Now all we have to do is calculate the difference between the dates in months. Unfortunately DAX doesn’t have a datediff function so we have to do it the Excel way, I am using a technique described in a blog post from Vidas Matelis for it.

= (YEAR([SalesDate]) - YEAR([First sales date]) ) *12
+ (MONTH([SalesDate])- MONTH([First sales date]))

This results in the column that gives us the months for each sales transaction compared to the date of introduction

Now we can compare the sales from these products in a pivottable:

But this of course is not the end of it, we now only see the sales for each month. We would like to see the sales since product introduction to the current date.

This is pretty simple formula:

=if(HASONEVALUE(Sales[MonthSinceIntroduction]),  CALCULATE([Sum of Sales], Sales[MonthSinceIntroduction] <= VALUES(Sales[MonthSinceIntroduction])) , BLANK())

This formula will check if only one month is selected in the current context, and for this single month calculate the [Sum of Sales] where the MonthSinceIntroduction is smaller or equal to the current MonthSinceIntroduction value we are in on the column.

This will result in this pivottable:

But hey.. hold on..why don’t we see values for the months we don’t have sales ? The dax formula should work. I had to dig deep to find an answer and had to call in the troops 🙂 (called Jeffrey Wang in this case).

This behavior is a caused by the MDX query that is being executed. Remember the data of the Pivottable is being queries by MDX. MDX will take care of retrieving the rows, column and filters, after it has retrieved the entire. Each DAX measure is being evaluated for each cell (or the cross section between rows, column and filters). One of the behaviors that MDX has  is that when there is no value in the table for a row / column combinations from the same table MDX Autoexists kicks in. Autoexists will make sure that if the combination between rows, columns and filters does not exists within the same table it will NOT execute the formula for that spot. Since MDX is designed for a Multidimensional model where for each fact table you have a separate dimension this made sense. For PowerPivot this makes a little less sense but still pretty simple to work around.

1 Use Crescent 🙂 that will use DAX to create the report and does not have MDX autoexist.

2 create a second table with values 0 to 25 in it and link it to the original table and use those values in the report. That will solve the problem:

Observe I did not change the formula at all.

Now I can create a chart for it as well: