DAX : Calculating monthly sales changes

Introduction

In my former blogpost about PQM analysis I've talked about the PQM analysis and how you can use the SUMX function to calculate the revenue by multiplying two columns : Quantity and Price. In this blogpost I want to calculate the monthly changes in sales.

Current situation

I've created a simple Calendar lookup table for analyzing the Sales data with a Date Column that is different than I'm used to built in data warehouse project. In data warehouse project you create a surrogate key like 20150908 and use this as a PK -FK relation between two tables. In PowerPivot you join on a normal dates like 8-9-2015. This is a best practice!

In the Sales data table I've created a date column as a calculated column (not field) and based on this column I join the SalesPeriodDate column with the calendar table (DateKey).

=DATE(Sales[Year]; Sales[Period];"01")
 

In the following diagram a representation of the join is presented:

I've already created a Sales Calculated Field with SUMX (see my former post):

TotalSales:=SUMX(Sales; Sales[Price] * Sales[Quantity])

The next step is creating a previous month calculated field, SalesPrevMonth :

       
SalesPrevMonth:=CALCULATE([TotalSales];DATEADD(Calendar[DateKey]; -1; month))
 

This resets the current filter context to the previous month and this results in the following data:

And here you can see the current month sales compared with the previous month. Now, We can calculate the variance in the monthly sales compared to the sales of the previous month.

       
 %CurrentPrevMonthSales:=DIVIDE([TotalSales] - [SalesPrevMonth]; [TotalSales])

Now let's take a look at the percentage of monthly changes :

And here you can see the monthly changes in percentage. I didn't like the 100 % in the month January and therefore I included a test whther SalesPrevMonth is empty. I adjusted the DAX expression a bit:

       
%CurrentPrevMonthSales:=IF(
                          NOT(ISBLANK([SalesPrevMonth]));
                          DIVIDE([TotalSales] - [SalesPrevMonth]; [TotalSales])
                          )

And now the result is this:

And now the calculation is disappeared for the column January. I can also calculate this per product ( I removed february and August):

Conclusions

With simple calculations you can have powerful insights.

Greetz,

Hennie