Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

DAX: Running Totals

Continuing my exploration of DAX, here’s a quick post on calculating running totals. I’ll be honest and say that I didn’t work out these expressions myself but got it from a newsgroup exchange between Colin Banfield and Karen Aleksanyan. In this case I want to calculate a running total from the beginning of time up to the current date (as opposed to from the beginning of the current year, as with a year-to-date). There are two possible approaches:

=’FactInternetSales'[Sum of SalesAmount](VALUES(‘DimDate'[FullDateAlternateKey])>=’DimDate'[FullDateAlternateKey], ALL(‘DimDate'[FullDateAlternateKey]))

=’FactInternetSales'[Sum of SalesAmount](DATESBETWEEN(‘DimDate'[FullDateAlternateKey], BLANK(), LASTDATE(‘DimDate'[FullDateAlternateKey])), ALL(‘DimDate'[FullDateAlternateKey]))

The first simply asks for a filter of all values from FullDateAlternateKey where the date is greater than or equal to the current row date. Don’t ask me why it needs a ‘greater than’ as opposed to ‘less than’, but that’s what it needs to work – possibly this is a bug. The second expression uses the DatesBetween, Blank and LastDate functions (interesting that the Blank function seems to work in the same way a null does with the colon operator in MDX in this case); it has one advantage over the first expression in that it doesn’t display errors when other columns from the DimDate table are used in the pivot table. Here’s what the output looks like with just dates on rows:

As with the original ytd calculation in my previous post on DAX, neither expression displays the expected values when looking at Years or Quarters, for example – you again just see the same value as Sum of SalesAmount. The really weird thing is that at the date level both calculations return different values when you have Years and Dates on rows to when you just have dates. The screenshot below shows the crossover between 2001 and 2002 with just dates on rows:

Now here’s what you see with Years present:

Notice how in the first screenshot the running total carried on across the year boundary, as you’d expect, but in the second case the running total is only calculated from the beginning of the current year. I’m not sure whether this is a bug (although I suspect it is) or expected behaviour; the same thing happens when you slice by Year too, which makes more sense:

As ever, I’ll find out what’s going on and post an update here…

UPDATE: Marius Dumitru has come to the rescue again. Here’s the working version that does everything I want:

=’FactInternetSales'[Sum of SalesAmount](DATESBETWEEN(‘DimDate'[FullDateAlternateKey], BLANK(),LASTDATE( ‘DimDate'[FullDateAlternateKey])), All(‘DimDate’))

Here it is working:

Written by Chris Webb

December 4, 2009 at 11:05 pm

Posted in DAX

11 Responses

1. I\’ve tried to get the first approach (using the \’>=\’ sign) to work but to no avail. Would you be willing to post a simple sample .xlsx file? Basically, I\’m trying to do the exact same running total but using a numeric field (Such as 1st place, 2nd place, 3rd place), instead of dates. I keep getting empty Pivot results… Thanks!

Vincent

June 15, 2010 at 2:39 am

2. I don\’t have the workbook any more, but the calculations were exactly as above. Can you post the expression you\’re trying to use?

Chris

June 17, 2010 at 9:23 am

3. Brilliant solution, my only issue is that I’m trying to do this with a Countrows Measure but produces an error. Please could you help? I’ve included the Measure below for your reference:
=COUNTROWS(
FILTER(
DISTINCT(
Registrations[Accounts]
),
Registrations[Accounts]blank()
))

Shaun

August 8, 2012 at 5:35 pm

• Apologies a typo in the formula:
=COUNTROWS(
FILTER(
DISTINCT(
Registrations[Accounts]
),
Registrations[Accounts],blank()
))

Shaun

August 8, 2012 at 5:40 pm

• Can you tell me what you’re trying to do with this calculation, exactly?

Chris Webb

August 8, 2012 at 10:29 pm

• Hi Chris, I think i have a problem similar to Shaun. I am trying to calculate the running total of a distinct count. So each date, it obtains a new distinct count of the dates so far displayed .

Here is what I thought I could do.

=CALCULATE(DISTINCTCOUNT(Table[Column]), FILTER(Table, [Date] <= Table[Date]))

The problem is that I want Table[Date] to refer to the row of the query and I'm not sure how to do that.

Any help would be appreciated.

Peter

November 23, 2012 at 7:56 am

• Hi Peter,

Instead of using Filter, it’s easier to use DatesBetween(). Your calculation will be something like this:
=CALCULATE(DISTINCTCOUNT(Table[Column]), DATESBETWEEN(Table[Date], null, LASTDATE(Table[Date])))

HTH,

Chris

Chris Webb

November 24, 2012 at 10:52 pm

4. Hi Chris, yes this worked well. Thanks!

Peter

November 29, 2012 at 6:57 am

5. I tried this solution(using AdventureWorks) and seems to work:

RunningSumSalesAmount:=CALCULATE(SUM(FactInternetSales[SalesAmount]);FILTER(ALL(DimDate);DimDate[DateKey]<=MAX(DimDate[DateKey])))

Jorge

January 2, 2013 at 11:34 pm

6. You posted this formula:
’FactInternetSales’[Sum of SalesAmount](DATESBETWEEN(‘DimDate’[FullDateAlternateKey], BLANK(),LASTDATE( ‘DimDate’[FullDateAlternateKey])), All(‘DimDate’))

If I plug that in, it is not recognized as a measure,,, was this shortcutted? is there supposed to be a calculate here? What is the proper syntax?

Chuck V

June 24, 2013 at 10:09 am

• This is using some alternative syntax that, while it should still work, I don’t like to use any more. It’s an alternative to using Calculate, but I now think it’s better to use Calculate and be clear. Also, this example is for PowerPivot v1.0, and in v2.0 and greater the All() function at the end isn’t required. So something like this should work:

Calculate([Sum of SalesAmount],DATESBETWEEN(‘DimDate'[FullDateAlternateKey], BLANK(),LASTDATE( ‘DimDate'[FullDateAlternateKey])))

One other possible cause of confusion is that I’m assuming that [Sum of SalesAmount] is a measure that has already been defined. If you don’t have a measure defined already, you’ll need to use the following:

Calculate(Sum(FactInternetSales[SalesAmount]),DATESBETWEEN(‘DimDate'[FullDateAlternateKey], BLANK(),LASTDATE( ‘DimDate'[FullDateAlternateKey])))

Chris Webb

June 24, 2013 at 10:25 am