PowerPivot case: what product made the most sales change in the last 7 days

Ok here is an interesting case that I got asked last week. The question was we would like to know the top 10 product who made the best change over the last 7 days.

I solved the problem based on adventureworks, this is the schema I used:

First thing that I want to do is get the rolling sum of the last 7 days. I start by creating the measure that determines the sales (remember splitting measure up will make your work much easier):

Sales:=SUM(FactInternetSales[SalesAmount])

Next up is creating the actual rolling sum:

Sales last 7 days:=
CALCULATE([Sales],
	DATESBETWEEN(DimDate[FullDateAlternateKey],
		DATEADD(LASTDATE(DimDate[FullDateAlternateKey]),-7,day),
		LASTNONBLANK(DimDate[FullDateAlternateKey],[Sales])
		)
)

Ok how does this work? It will calculate the Sales measure for all the dates between the lastdate in the current pivottable to 7 days before the lastdate. Observe that I use LASTNONBLANK instead of LASTDATE that will make sure that I actually get the last date where I have a value for the [sales] measure. It could be that when we use this measure against something else then dates that not every product/Dimension had sales on the same dates. Make sure what you know what the exact requirements are, using LASTNONBLANK instead of LASTDATE could give you wrong results if you are not paying attention.

Especially the part in the current pivottable is important, lastdate will automatically search for the last date in the cell, this would also work if you don’t put the dates on rows and would just use the slicer.

Ok to make our report nicer I actually only want to show the last 7 days based on the selection and data available:

So I wrap the previous measure with an IF statement:

Sales only last 7 days:=
if(CALCULATE(DATEADD(LASTNONBLANK(DimDate[FullDateAlternateKey],[Sales]),-7,DAY)
			, ALLSELECTED()) 
	< = 
	LASTNONBLANK(DimDate[FullDateAlternateKey],[Sales])
	,
		[Sales last 7 days]
		, BLANK()
	)

This will now only show the sales for the last 7 days based on the selection. What we want to do here is compare the last date in the current selection against the last date for the current row. I want to get the last date for  the current selection, hence I wrap the LASTNONBLANK with a calculate to change the context from the current row context to ALLSELECTED (meaning the entire pivottable). The LASTNONBLANK(DimDate[FullDateAlternateKey],[Sales]) will return the last date where we have sales for the current selection.

Now I swap out the date for products and add two more measures that shows us the sales of the last 7 days and one that trails by one day and the percentage between the two. This shows us who changed the most rapidly because I ordered the pivottable by percentage change.