Chris Webb's BI Blog

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

DAX: Running Totals

with 11 comments

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:

image

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:

image

Now here’s what you see with Years present:

image

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:

image

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:

image

Written by Chris Webb

December 4, 2009 at 11:05 pm

Posted in DAX

11 Responses

Subscribe to comments with RSS.

  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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,190 other followers

%d bloggers like this: