Use Time intelligence functions to do a running sum of the last 6 months with PowerPivot

Today I did a PoC using PowerPivot, both to show Self Service BI with PowerPivot for SharePoint but also as a datasource for SSRS reports. I used DAX to solve all difficult request, mostly making use of the Time Intelligence functions.

One of the requests was if we could show a running sum of the last 6 months. I had not done that before, so today we take a look at how to do this with DAX. It appeared to be rather easy 🙂

Again we use the almighty Calculate function to change the context of the row we are in. We want to do a sum of all the rows of the last 6 months of data. We use the DATESINPERIOD function to get the last 6 months of dates.

The function DATESINPERIOD has 4 parameters, first is the column containing the datarange, second is the start date. We can get it by getting the last date in context, we use LASTDATE to get this. As last two parameter we can give intervals to subtract or add from the start date. In our scenario we want to subtract 6 months from the last date.

This gives the following formule:

=IF(ISBLANK(sum(FactInventory[DaysInStock])),BLANK(),
	CALCULATE(sum(FactInventory[DaysInStock]),
	DATESINPERIOD(DimDate[DateKey],
	LASTDATE(DimDate[Datekey]),-6,MONTH)))

Again not too hard 🙂

I put in a ISBLANK to check if we have values for the current month, we are not interested in the future 🙂

This gives us the following result:

I decided to check out the DATESBETWEEN as well, the DAX function below gives the same result:

=IF(ISBLANK(sum(FactInventory[DaysInStock])),BLANK(),
	CALCULATE(sum(FactInventory[DaysInStock]),
	DATESBETWEEN(DimDate[Datekey],
		FIRSTDATE(DATEADD(DimDate[Datekey],-5,MONTH)),
		LASTDATE(DimDate[Datekey]))))

The DATESBETWEEN function can be used to return a table of dates between a start and a end date.
As end date we need to get the last date that is available in the current context. We can determine this by doing LASTDATE(DimDate[Datekey]). The first date is a little more confusing. We need to get the date 6 months ago, of course we can use the DATEADD function. This will return us a a set of dates of the current context, in our sample we get a set of all dates in the month. Using firstdate we get the first date of this set to start our datesbetween function.