First steps in DAX: Year-To-Date

With the release of the latest CTP of PowerPivot, DAX has gained a lot of new functions (mostly to do with time calculations), so over the weekend I decided that it was time to sit down and start learning it properly. A lot of other people out there are doing just the same thing – Vidas, for example – and like them I thought I’d blog about my experiences while I was checking it out. So far I agree with Shaun Ryan that anyone with a background in MDX will have an advantage when it comes to understanding DAX, but there are as many differences as there are similarities and it’s still a bit tricky to understand what’s going on.

I would like to point out that the code here doesn’t necessarily represent best-practice for DAX (I’ve only been using it for a short time, remember!) and in some cases the code will have to change before RTM because DAX itself will change; hopefully, though, these examples will help anyone else out there who, like me, at the bottom of the learning curve. In all this blog post I’m using a very simple PowerPivot model based on the DimDate and FactInternetSales tables from the 2008 version of the Adventure Works DW database, with the two tables joining on the OrderDate key.

I thought I’d choose year-to-dates as the subject of this first blog post because it’s a common calculation and relatively easy to understand. There is, of course, a DAX function specifically for year-to-date calculations: TotalYTD. Here’s what my first attempt at a DAX year-to-date calculated measure looked like:

TotalYTD(Sum([SalesAmount]), ‘DimDate'[FullDateAlternateKey], All(‘DimDate'[FullDateAlternateKey]))

I would have put in some line breaks but of course DAX doesn’t allow them (yet? hopefully this will change before RTM). Step by step:

  1. TotalYTD( – the year-to-date function itself
  2. Sum([SalesAmount]) – the value we’re going to be summing up, ie the sum of the [SalesAmount] column
  3. , ‘DimDate'[FullDateAlternateKey] – the date we want to do the year-to-date sum up to, which can be found in the [FullDateAlternateKey] column of the DimDate table
  4. , All(‘DimDate'[FullDateAlternateKey]) – which is an extra filter condition that just needs to be there to make things work. If it ain’t there, the calculation won’t work, but it doesn’t really serve any purpose and it won’t need to be there at RTM. Howie Dickerman of Microsoft pointed this out in his session at PASS earlier this month and if I hadn’t picked up this tip I’m sure I would have got absolutely nowhere with DAX by now.

Here’s a screenshot of the calculated measure in action:


As you can see, it works well when we’re looking at dates. But when we start to introduce years and quarters you can see we don’t get the results we might expect:


Hmm, cue several hours of head-scratching on how to fix this. After a lot of trial and error, I found that by creating a second calculated measure like this:

‘FactInternetSales'[YTD Sales](LASTDATE(VALUES(‘DimDate'[FullDateAlternateKey])), all(‘DimDate’))

Worked. What I’m doing here is forcing the measure to display the value of the [YTD Sales] calculated measure for the last date in the current context. Here’s what it displays (note the value for Quarter 2):


Various other similar approaches didn’t work, though, and the obvious next step of using LASTDATE(VALUES(‘DimDate'[FullDateAlternateKey])) inside my original calculated measure didn’t work either.

Given that PowerPivot doesn’t allow you to hide calculated measures (another thing that, in my opinion, needs to be fixed before RTM – breaking up calculations into smaller steps is a useful thing to do for debugging and sharing code) I don’t particularly like this two step process. I’ll post an update here when/if I find a way to do this in a single calculation; if anyone else out there finds out how to do this, please leave a comment!

UPDATE: Marius Dumitru has given me the answer to my problem. Here’s a version of the YTD calc that works on all time selections:

=TotalYTD(Sum([SalesAmount]), ‘DimDate'[FullDateAlternateKey], All(‘DimDate’))

The difference here is the All function is taking the whole DimDate table rather than just the [FullDateAlternateKey] column. I’m currently trying to work out why this is important… In the meantime, here’s a screenshot of it working:


8 responses

  1. It\’s interesting to note that, if you select more than one year (moving Year on a slicer, for example), the YTD will be always the one of the last year in the selection. Used to MDX like I am, this seems strange and non-intuitive at first, but maybe users will prefer (and understand?) this behavior.

  2. I hadn\’t tried that, but you know I think it makes sense to have it work that way. DAX is growing on me, slowly…

  3. Hi Chris,When I try to replicate your first ("easy") formula I get, : "a column specified in the call to function \’LASTDATE\’ is not of type DATE. This is not supported."Here\’s my formula:=TOTALYTD(sum([SalesAmount]), \’SalesDate\'[FullDateAlternateKey], All(\’SalesDate\'[FullDateAlternateKey]))

  4. So, being a DAX newbie, I am getting confused with syntax left right and centre. I keep seeing people showing code FullDateAlternateKey and DimDate in various examples. Since I\’m not a programmer nor Access trained, can I assume that those are referencing tables / rows and can be named anything? Reason being as I\’ve seen a posting where someone said for the current release all Date/time functions had to include (for his example) All(DimDate), so I was unsure if it was the All that needed or the All(DimDate) or some odd combination. I\’m just trying to sort out orders by InvoiceDate. No Dimdates, no Dim entries, no Alternatedate keys, no programmer par for the course syntax or labelling defaults. So how should it read in the real world?

  5. John, in this example FullDateAlternateKey is a column and DimDate is a table; both come from the Adventure Works DW sample database for SQL Server. So yes, you\’re right, you can use your own column and table names instead, including in the All() function.

  6. Hi Chris,

    Thanks a lot for sharing this. I encountered the same issue and found a different solution.


    Obviously I have a different Time Dimension, including a month start date. Day equals your FullDateAlternateKey.
    With these I could have 2 levels in my hierarchy, for a third level you have to nest again. Your solution is better, because there is no need for that. Just would like to share, because it took me also a while to figure it out.

    Grtz, Frits Nagtegaal
    Building BI Solutions for SAP R/3 without SAPBW

  7. I had exactly same difficulty with understanding how to work with TOTALYTD and got it resolved using hint from your article -> TotalYTD(Sum([SalesAmount]), ‘DimDate’[FullDateAlternateKey], All(‘DimDate’)). Thanks a lot!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: