Use PowerPivot DAX and Excel to create a sparkline with running total last 12 month

Got an interesting question today. The question was about creating sparklines for all the country’s that shows a running total of sales for the last 12 months (Rolling 12 months) based on the year that I select from a slicer. I use the trick I blogged about before to work with sparklines and PowerPivot, I would recommend reading it for some background.

First I want to start by creating the sheet that I want to use as a report. On this sheet I start by creating the slicer that I want to use, In this case Calendaryear:

Next I create a second sheet where I want to collect my data for the sparklines.

Because we want to show the running 12 months per year I would like to use only the months of one year that is the baseline of the running month. So we hook up the slicer from our report to the pivottable in the other sheet. Select the slicer, go to options and click on pivottable connections, here I can select the pivottable from the other sheet to be filtered as well:

Next we want to create a measure that creates our running total:

=if(COUNTROWS(values(DimDate[CalendarYear])) = 1,
			CALCULATE(SUM(FactSales[SalesAmount]),
				DATESBETWEEN(DimDate[Datekey],
					DATEADD(FIRSTDATE(DimDate[Datekey]),-11, MONTH)
					, LASTDATE(DimDate[Datekey])))
			,blank())

What this DAX measure does is first of all check if only one year is selected using:

COUNTROWS(values(DimDate[CalendarYear])) = 1,

If more than one year is selected we return blank(). If only one year is selected we calculate the running total by doing a sum of FactSales[SalesAmount] by creating a date range using DatesBetween.

The start date of the range is determined by getting the first day of the current month (we know this because of the current row context) and then go back 11 months before that:

DATEADD(FIRSTDATE(DimDate[Datekey]),-11, MONTH)

to the last date of the current month:

LASTDATE(DimDate[Datekey])

Using this in combination of calculate the sum will now calculate over the entire date range.

Now we have the data set up we can create the sparklines. First we need to copy the rows and paste them to the report and create the sparklines on the report page, check the previous blog post on how to create the sparklines.

I have uploaded the workbook for download here.