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

By | March 30, 2011

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.

 

  • Nice post and use of sparklines. I was a bit confused with the running total name because I was expecting cumulative totals and the display of the sparklines confused me with them going up and down or in the case of some countries trending down. Then I realized you are doing a rolling 12 month value.

  • Reuvain

    @Dan,
    Thanks for that clarification.
    I totally missed the point that it was a rolling 12 month sum.
    Now the post makes much more sense to me.
    RK

  • Kasper de Jonge

    Thx @Dan English I updated the title. The person asking me the question talked about running total and not rolling months 🙂

  • BillD

    Is there a way to define a dynamic MDX set to always display 12 individual months based on a slicer choice of year and month? Example: choice of March 2011 would show monthly detail of April 2010 through March 2011. Thanks

    • Kasper de Jonge

      No there is a real good way to create a MDX set. What you can do is create a calc column that can contain a DAX formula to determine on the fly what should be in the set and what should be out. It is not really the same but could help you.