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

By | June 18, 2010

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],