Chris Webb's BI Blog

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

First steps in DAX: Year-To-Date

with 8 comments

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:

image

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:

image

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):

image

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:

image

Written by Chris Webb

December 1, 2009 at 11:33 am

Posted in DAX

8 Responses

Subscribe to comments with RSS.

  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.

    Marco

    December 7, 2009 at 4:37 pm

  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…

    Chris

    December 7, 2009 at 8:35 pm

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

    greg

    January 2, 2010 at 11:21 am

  4. Sorry to ask the obvious question, but what type has PowerPivot given the FullDateAlternateKey column?

    Chris

    January 2, 2010 at 3:54 pm

  5. 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?

    John

    February 3, 2010 at 8:56 pm

  6. 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.

    Chris

    February 4, 2010 at 1:29 pm

  7. Hi Chris,

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

    TOTALYTD(TOTALYTD(SUM(CRV_REVENUE[INVOICE_VALUE]),D_TIME[MONTH_START_DATE]),D_TIME[DAY])

    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
    NewFrontiers
    Building BI Solutions for SAP R/3 without SAPBW

    Frits Nagtegaal

    October 5, 2010 at 1:33 pm

  8. 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!

    aedna

    February 17, 2011 at 11:26 pm


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,301 other followers

%d bloggers like this: