How Do We Get the Prior Year's "Year to Date" Number in DAX?

YTD is easy with “standard” calendars, but the previous year’s equivalent is a non-obvious formula.

Back in the Saddle!

imageWhat’s this, you say??  Yeah, it’s Rob, and I’m here with an actual formula post!  Woo woo!  Starting the year off right, as they say.

We’ve got a lot going on early this year, so expect to see a number of announcements and reminders in the coming weeks (one of which later THIS week, and it’s super exciting for me at least).

So with that in mind, we’re ALSO renewing our commitment to “meaty” posts – the stuff that brings you here in the first place.

How Are We Doing THIS Year Versus the Same Time LAST Year?

Hey, this is a pretty common question, and a pretty common need.  But there’s no DAX function that just DOES this.

For instance, getting a “Year to Date” calculation for, say, Total Sales, is pretty straightforward:

[YTD Sales] =

CALCULATE([Total Sales], DATESYTD(Calendar[Date]))

Very straightforward, and it gives us this:

How Do We Get the Prior Year's "Year to Date" Number in DAX?

Ah the magical DATESYTD function!

Aside:  Requires a Standard Calendar, AND a Properly-Constructed Calendar/Dates Table!

Even the magical DATESYTD function comes with pre-requisites:

1) Your business must run on what we call a “Standard” calendar.  What’s a “standard” calendar, you ask?  Well, the key question here is “in your business, what qualifies as a month?”  For instance, if you want to compare April of this year to April of last year, what defines “April?”  Can you just look at a regular wall calendar as a reference for what defines April?  If so, you have a Standard calendar – even if your year ends in June rather than December.  But if not, and your “months” aren’t really months at all, but you have “periods” instead, well, you have a Custom Calendar.

DATESYTD will not help you if you have a Custom calendar, and neither will this blog post, sadly, because for the moment, I only have time to cover the standard calendar version.

(For more on custom calendars in the meantime, try our newly-released 2nd edition book, which has been updated quite a bit since the best-selling 1st edition).

2) You must have a well-constructed Calendar/Dates table.  And what qualifies as well-constructed?  Turns out this is covered quite succinctly in the reference card, which you can download for free, so I won’t go into it here.

2a) OK, and you ALSO need to then USE that Calendar/Dates table on your pivot.  There’s a great post on this topic, so make sure you glance over it if you aren’t familiar with this concept.

Now for Last Year’s YTD Number!

Back to the point of this post:  it turns out that you can “nest” a DATESYTD inside of a DATEADD!

Try this:

[Prev Yr YTD Sales] =

CALCULATE([Total Sales], DATEADD(DATESYTD(Calendar[Date]),-1,Year))

And we get:

How Do We Get the Prior Year's "Year to Date" Number in DAX?

Bam!  That’s What We’re Talking About Willis!

Basically, what happens in this formula is that the DATESYTD “runs” first, and “finds” the dates in the calendar starting from the beginning of the year up through the “max” date in the current filter context of the pivot.  Then that range of dates is handed to DATEADD, which then “shifts” that range of dates back by one year.

A simple formula once you know you can nest these two.

More Complexities

Let’s say you’re halfway through a given month – for example, pretend today’s date is July 15, 2016, and I want to know how we’re doing, year to date, versus the same time period of 2015.

In that case, I don’t want ALL of July 2015 included in the comparison!  That would be “unfair” to 2016, because I’d have less time in 2016 than the comparison period in 2015.

But if my Calendar table contains all of the dates for July 2016 – it goes slightly into the future and already has rows for July 16, 17, and so on – well guess what?  The formula above WILL include the entirety of July 2015 in the comparison.

I’ve never liked this about the built-in date intelligence functions, and this is why I always prefer Calendar tables to be “trimmed” – meaning, the latest date included in the Calendar table matches the latest date for which I have real data (like Sales transactions for instance).  A trimmed calendar avoids this problem, and limits my 2015 comparison period to properly “match” my partial month in 2016.

But for some reason, every other DAX pro I’ve talked to prefers Calendar tables to run through the end of the current year.  I don’t quite get why they prefer it that way, but hey, I know when I’m outnumbered that I should at least entertain the possibility that I am wrong.  So if you follow the advice of others, you will not have trimmed calendars (which are admittedly tricky to pull off, since they need to update every day as you get new data), and you will need another fix.

Never fear, in this case you just nest a third function:

[Prev Yr YTD Sales Trimmed]=

CALCULATE([Total Sales],
           DATEADD(
                   FILTER(DATESYTD(Calendar[Date]),[Total Sales]>0),
                   -1,Year
                  )
          )

Basically, all we did was add an intermediate step, via the FILTER function.

So now the order of execution goes:

  1. Find the range of dates YTD according to your Calendar table (which may include dates for which you don’t yet have data).
  2. Then FILTER those dates to exclude dates for which you do NOT have data – this trims those future dates, like July 16 in our example.
  3. NOW shift those remaining dates back one year.

 

Of course, even THIS has problems, because if you have legitimate historical dates for which there is no data, the FILTER will remove those too, and then your DATEADD will blow up on you.

Geez, wouldn’t it be better to just have a trimmed calendar, folks? Smile

Here we go, try this:

[Prev Yr YTD Sales Trimmed]=

CALCULATE([Total Sales],
           DATEADD(
                   FILTER(DATESYTD(Calendar[Date]),
                          Calendar[Date]<=
                          LASTNONBLANK(Calendar[Date], [Total Sales])
                         ),
                  -1,Year
                  )
          )

Phew, I think that works Smile