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:
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!
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?
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()
))
Apologies a typo in the formula:
=COUNTROWS(
FILTER(
DISTINCT(
Registrations[Accounts]
),
Registrations[Accounts],blank()
))
Can you tell me what you’re trying to do with this calculation, exactly?
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.
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
Hi Chris, yes this worked well. Thanks!
I tried this solution(using AdventureWorks) and seems to work:
RunningSumSalesAmount:=CALCULATE(SUM(FactInternetSales[SalesAmount]);FILTER(ALL(DimDate);DimDate[DateKey]<=MAX(DimDate[DateKey])))
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?
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])))