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

  Subscribe to PowerPivotPro!
X

Subscribe

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology. 

This Post Has 42 Comments

  1. I was just working on something similar today and used the TOTALYTD time intelligence function to get the period YTD eg for a company with year end 31/03/yy then that would be :

    [Fiscal YTD Sales] := TOTALYTD ( [Sales Amount], ‘Date'[Date], “31/03”)

    The “31/03” is because my local language settings are UK. YOu should amend this to your own settings.
    Without using DAX built in time intelligence functions this could have also been done for the current period using as your post DATESYTD like this

    [Fiscal YTD Sales] := CALCULATE ( [Sales Amount], DATESYTD ( ‘Date'[Date], “31/03”) )

    Then with this base measure [Fiscal YTD Sales] I then used the function SAMEPERIODLASTYEAR
    Like this
    [PY Fiscal YTD Sales] := CALCULATE ([Fiscal YTD Sales], SAMEPERIODASTYEAR ( ‘Date’[Date] ) )

    Is this correct and if so how and why your version in your blog ?

    Ps for beginners link the date in your Sales Fact table to a full ‘Date’ dimension table to the field [Date] which should be a sequential order of all the daily dates within your dataset range.

    1. Hi David, see my reply to Cwayne below. TOTALYTD and DATESYTD are both time intel functions, and I believe them to be identical under the hood. (TOTALYTD still ends up using a CALCULATE behind the scenes).

      I also personally dislike using a CALCULATE function on top of another, because the impact on filter context becomes murkier. I prefer to do the Last Year YTD measure all at once, rather than piling CALCULATE on CALCULATE. Stylistic perhaps. If your approach is working for you, I encourage you to continue 🙂

      1. Rob, you mentioned not liking a CALCULATE inside a CALCULATE. I have to admit I actually do that, mainly when one measure is a natural extension of another.

        E.g. if I already have a [Bike Sales] measure that uses CALCULATE, and someone comes along and asks me for a Green Bike Sales measure, I would typically do CALCULATE([Bikes Sales], FILTER([Color]=”Green”) rather than doing CALCULATE([Sales], FILTER([Product]=”Bike” && [Color]=”Green”).

        Obviously a simple example, but there definitely seems to be a pro/con trade-off here. By wrapping my green bike sales around [Bike Sales], I ensure that any changes to the definition of [Bike Sales] propagates down to derived formulas (portable formulas!). But on the flip side, I am layering CALCULATE inside CALCULATE in a way I would never do in a single formula. I haven’t run into performance issues, but your comment has me thinking about what the best practise here might be (if indeed there is one).

        1. Leonard, those cases don’t trigger my sense of unease. Column=Value filters, the kind I call “simple filters” – the interaction of piling those on top of each other is pretty clear to me. I would do exactly the same thing as you describe, in the case of Green Bike Sales.

          What I *don’t* like to do, is perform advanced manipulation of filter context in multi-layered fashion. A DATESYTD measure nested inside a SAMEPERIODLASTYEAR measure for instance. Even that, though, is likely just a personal quirk of mine rather than a best practice rooted in good reasons.

    1. The thing about the time intel functions is that many of them are just “re-skinned” versions of the others. TOTALYTD(measure,…) , as far as I can tell, is exactly the same as CALCULATE(measure, DATESYTD(…))

      To keep things simple for myself and students, I tend to stick with the more “primitive” versions of the functions, but that’s a stylistic choice as much as anything.

      And sometimes these alternate functions contain interesting, quirky, extra logic that I can’t clearly see because it’s hiding behind the scenes. I think PARALLELPERIOD was one of them, SAMEPERIODLASTYEAR is probably more straightforward but again I haven’t personally used it in a long time. Stylistic choice, again, and I see nothing wrong with using the other functions. Primitives are just more my style.

  2. Rob, in the [Prev Yr YTD Sales Trimmed] there is an issue with the use of LASTNONBLANK. Such a function is a table function that performs a context transition, so calling it within an iterator (the FILTER) evaluates only one date at a time. In practice, by writing
    LASTNONBLANK ( Calendar[Date], [Total Sales] )
    you are getting the same result as if you write:
    IF ( NOT ISBLANK ( [Total Sales] ), Calendar[Date] )

    Maybe you get the same result as you want in this way, but if you have some date with a blank value in the middle of two days with some value, the DATEADD function will not work (because it does not support non-contiguous selections)

    1. Thanks Marco, this is interesting, and always glad to see your name in the comments feed! A few quick comments/thoughts:

      1) Yeah, serves me right for leaving the world of trimmed calendars and trying to do things the other way, all at the end of a post that I planned to end long before I dragged myself into all of the special cases.

      2) That said I did test the formula and it did seem to be working properly, I will try that again when I get home.

      3) Are you sure you guys don’t want to partner up in some way where you guys are the Graduate level and I’m the Intro level? 🙂

      1. Let me know if you find the issue – I tried the formula and it was having the issue I described – for the rest, Alberto will be at MS Data Insight Summit and I will be at PASS BA Conference – we’ll have time to talk 🙂

        1. You can calculate the last relevant date using a variable to prevent the non-contiguos date range.

          [Prev Yr YTD Sales Trimmed]:=

          var LastRelevantDate = LASTNONBLANK ( ‘Calendar'[Date], [Total Sales] )

          return
          CALCULATE ( [Total Sales],
          DATEADD (
          FILTER ( DATESYTD ( ‘Calendar'[Date] ),
          ‘Calendar'[Date] <= LastRelevantDate
          ),
          -1,
          YEAR
          )
          )

    2. Rob, the subject was just what I was looking for but using your solution I did get the non-contiguous error. Do you have a different solution I can try?

      1. While i have not tried this, the following should work:

        [Prev Yr YTD Sales Trimmed] :=

        CALCULATE([Total Sales],
        SAMEPERIODLASTYEAR(DATESBETWEEN(Calendar[date], STARTOFYEAR(Calendar[Date] ),
        LASTNONBLANK(Calendar[Date], [Total Sales])

        )
        )

        Couple of other points though:
        You may want to enter some error testing/handling to be sure ‘LASTNONBLANK’ returns a value because if it returns blank ‘DATESBETWEEN’ will extend the dates returned to the last date in your calendar table. Probably not what you want.

        Also be sure to ‘Mark as Date Table’ your calendar table so the time intelligence functions work correctly.

        Personally i would never use a trimmed calendar especially if i am using the time intelligence functions. They work by shifting the calendar[date] column values visible in the filter context – not by performing math. So with a trimmed calendar you would need to be careful not to shift into the non-existent future or past. Since they do shift dates in filter context you need to include all the dates for the prior year in your calendar table. I also think getting used to and using the specialized time intelligence functions improves code readability and therefore maintenance even though they are mostly sugar syntax built on variations of the date function primitives. Having said that I agree though that it is a personal style preference.

        1. Hi Matthew, I seem to have gotten your formula working … seems to be just what I needed. Thank you.

          I’m far from an expert, but days in my calendar table were making the formula work strangely, and I believe it had to do with the LASTNONBLANK part you discussed above.

          I added the IF ISBLANK portion as per below, and it seems to have eliminated the data appearing for a previous year that shouldn’t have. Seems to be working as intended for me right now.

          Prev Yr YTD Sales Trimmed:=CALCULATE([Total Sales],
          SAMEPERIODLASTYEAR(
          DATESBETWEEN(dCalendar[date],STARTOFYEAR(dCalendar[Date]),
          IF(ISBLANK(LASTNONBLANK(dCalendar[Date],[Total Sales])),STARTOFYEAR(dCalendar[Date]),LASTNONBLANK(dCalendar[Date],[Total Sales])))))

  3. What if, historically, you only have transactions on a Saturday in November and December but not throughout the rest of the year. Will those blank dates throw this off?

  4. I was actually working on the same issue last week and ended going with something that looked like this:

    =
    TOTALYTD (
    [TOTAL SALES],
    DATESBETWEEN (
    Dates[Date],
    SAMEPERIODLASTYEAR ( FIRSTDATE ( Dates[Date] ) ),
    SAMEPERIODLASTYEAR ( LASTDATE ( Dates[Date] ) )
    )
    )

    Not sure if this is efficient or the best way to do it, but it worked for me.

    1. Syntactically it will work but logically it doesn’t solve the point Rob is bringing up. The code above would include the last date of the current filter context which unless you are using one of Rob’s trimmed calendars will always be the last day of the month. Rob wants more apple to apple granularity meaning the previous year comparison is for the same exact number of days as the current ytd total (mid period).

      However the validity of Rob’s comparison for periods prior to the current one is debatable. The formula he wrote and the one i posted above are logically questionable and probably not what would be desired by management.

    2. the datesbetween combined with sameperiodlastyear is the clearest for me and works for non standard calender years like a fiscal year. I just adopted it for a full previous years (12months) sales replacing firstdate & lastdate with startofyear & endofyear respectively.
      Maybe not the most efficient for the DAX engine but good.

      Just i had problems with ParallelPeriod which appears to only work on calender years and not fiscal years. Has anyone managed to use parallelperiod for non standard calender years ?

  5. Rob, the [Last YTD/MTD Sales Trimmed] formula is EXACTLY what I’m looking for right now but when I try your formula I’m getting the dreaded “DATEADD only works with contiguous date selections” error. Any ideas what causing the error?

  6. Hi guys,

    Great info – very helpful. I created a budget vs actual report using Power Pivot. I manually imported the budget data and through a series of relationships i can view both the actual and budget by month, department and account. The issue arises when i try to show the variance between the actual and the budget. If i manually create a Pivot Table, I can use the calculated field function which will be ‘budget’ minus ‘actual’ and i get the variance.

    I am struggling here so any help would be greatly appreciated.

  7. What happens if last year was leap year? Will the extra day in February “just work” or be left out? The official reference says DATEADD “Returns a table that contains a column of dates, shifted either forward or backward in time by the specified number of intervals from the dates in the current context.” So there is no Feb 29 in the current context

  8. Hi,

    How do we use Quarter to Date. Previous quarter to date and Previous year same quarter to date?

    I am using this for my YTD calculations. Anything that i could modify to get the quarter values.

    DATESBETWEEN(Dates[Dates],DATEADD(LASTDATE(Year_Period[Next_Month_Start_Date]),MAX(Year_Period[Fiscal_Period])*-1,MONTH),LASTDATE(Year_Period[Month_End_Date])

    1. Why not use time intelligence functions(?) Much easier to read and maintain…

      QTD := TOTALQTD ( [Total Sales], Calendar[Date] )
      Previous QTD := TOTALQTD ( [Total Sales], PREVIOUSQUARTER(Calendar[Date] ) )
      Previous Year Same Quarter := TOTALQTD ( [Total Sales], SAMEPERIODLASTYEAR( Calendar[Date] ) )

  9. I’ve been banging on this exact question for a couple of days wondering what type of convoluted logic I needed (and creating some horrific Frankendax experiments in the process.)
    Thank you for surfacing the problems and solutions with such clear and helpful explanations.

  10. Personally I’m a big fan of all the Custom Calendar and calculated columns tricks on Custom Calendars. A little more setup on the backside, but then writing the “Previous YTD Sales” or any other similar function is a snap.

    My Custom Calendar Table includes a column for the number of the Day in the year (“Day of Year”) (e.g. today May 8th is the 129th Day of 2016). First I created a measure that returns the Day of Year for the latest sale of the current year,

    Latest Day of This Year:=CALCULATE(LASTNONBLANK(Calendar[Day of Year], [Total Sales]), Calendar[YEAR]=YEAR(TODAY()))

    then a calculated column on my calendar table “Is Prev YTD Date” =IF(YEAR(TODAY())-1=[YEAR]&&[Day of Year]<=[Latest Day of This Year], TRUE, FALSE)

    Then my Previous YTD Sales measure is simply = CALCULATE([Total Sales], Calendar[Is Prev YTD Date])

    (I used to include an "= TRUE", as in Calendar[Is Prev YTD Date]=TRUE , but found I didn't even need to include that since the column itself returns only TRUE or FALSE values)

    (My fiscal year starts Jan 1, but this could be easily modified to account for a Fiscal Year start date other than Jan 1)

    What do you think of this approach? My understanding is that using calculated columns on Lookup Tables (Like the Calendar table) isn't significantly detrimental to performance or size if the cardinality on the calculated field is low. Am I wrong about this? I used to wrestle (and usually lose!) with all the time intelligence functions until I started this approach and now I have tons of calculated columns on my Calendar Table for all sorts of custom time periods that I need and then my measures are simply = CALCULATE([Total Measure], Calendar[Custom Time Window]).

    1. I realized that this approach works as long as date fields aren’t on my axes. The rows on the table in my report is my list of customers, and some of them don’t have any YTD Sales, so I was running into the problems mentioned above with non-contiguous dates. I use “Today’s Day of Year” instead of “Latest Day of This Year” using CALCULATE(MAX(Calendar[Day of Year]), Calendar[Date]=TODAY()) when I need the report to include dates on rows. But perhaps it could still work if a FILTER(ALL…. or some such approach was used in the “Latest Day of This Year” Measure…? I personally haven’t had a need for this but if I did that’s where I’d start playing around.

    2. Interesting, but if you already have a Calendar table with all contiguous dates for the range you need to report on(this year and prior and future dates), not sure how that is easier than:

      YTD Total Sales := TOTALYTD( [Total Sales], Calendar{Date] ) or equivalent: CALCULATE ( [Total Sales], DATESYTD( Calendar[Date] ) )
      YTD Total Sales Prior Year:= TOTALYTD( [Total Sales], SAMEPERIODLASTYEAR (Calendar[Date] ) ) or equivalent: CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR( DATESYTD( Calendar[Date] ) ) )

      I think putting any custom date shifting you want to do is best put in the function instead of a table. But a personal preference perhaps.

  11. I have probelm with balance sheet. I have some accounts on 31/12/2012. Now, I have to find formula to give me amount for balance sheet (BS) on day 31.05. 2015. I have to sum all numbers accounts from 31/12/2012 until 31.05.2015. Which formula is the best to achieve my request.
    <thanks.

  12. I have a little different requirement. I work in a long running project environment say 3-4 years long projects. We monitor completion till date, t0 being in the earlier years. I need to calculate Completion till date. Is there a way I can do this.

    1. If you are a brave soul, the measure formulas can be applied in the workbook “ch14_TimeIntelligence.xlsx” available in the downloads for the book, “Power Pivot and Power BI”. If nobody posts more, at least this can get you started.

  13. That’s a great post, Rob! Exactly what I was looking for to have my “Same Date Last Year” figure. However, when I drop the Measure in my Pivot Table, I get an error message: FUNCTION DATEADD only works with contiguous date selections

    My Measure is as follows:

    PickUp(RN)SDLY:=CALCULATE(
    [SUM RN], DATEADD(FILTER(DATESYTD(DateDimension[Date]),
    DateDimension[Date]<= LASTNONBLANK(DateDimension[Date],
    [SUM RN])), -1,Year)
    )

    What am I doing wrong?

  14. Hey Rob, I have tried your way of getting Prior Year to Date until a specific date in a month (August 9 2015), but it works partially. In this case I’m comparing this year YTD sales (August 9 2016) and Prior YTD sales (August 9 2015) day by day and it works well, it’s just that at the last row (August 9), it shows me the sales for the whole August 2015, not the sales until August 9 2015 as expected. I modified your function at the first part cause if I select [Total Sales] measure, I don’t just get the data incrementally until August 9 but also I get a total value for 2015 and 2016 years.

    The function is this :
    Prev Yr YTD Sales Trimmed = CALCULATE(Sum(Sales[Sales]),’Calendar'[Date],DATEADD(FILTER(DATESYTD(Calendar[Date]),Calendar[Date]<=LASTNONBLANK(Calendar[Date],Sales[Total Sales])),-1,Year))

    Thanks your feedback !!

  15. Why not simply identify the current date, identify the first date of the current year, do the same for the prior year and then use the DatesBetween function to define each YTD period accordingly?

  16. Hi Rob,
    great post, thx!
    I hit a wall with one particular calculation of YoY growth rates (GR).
    I have a measure, which calculates GRs and I want to figure out:
    – how to add a slicer with ranges of GR: 10% – to quickly filter only outperforming months?
    – what if I have a list of 100 products sold in each month and I would like to have a column with a number of products, for which YOY GR is in the range of the slicer? say for Jun16 25 products had YOY GR of 5-10%. having this number of 25 I could double click on it to see the exact items – useful.
    Would appreciate if you could share your thoughts.
    Cheers,
    Gene

  17. It seems I’m encountering in the issue you mentioned where my YTD comparisons are summing to the end of the period/month when the current date is mid-month. I created my date table using CALENDARAUTO() in this report. It seems I should have used a Power Query date table so that the first and last dates in my date table match the respective dates in my fact table. Is there a way I can trim my current date table without creating a new date table from scratch?

  18. Your article helped me solve a calculation I was having trouble with.

    I just found out I still have an issue come 2/1/2017. For this paticular report we have a rolling 13 months of data. So on Feb1 2017 when this report runs I will have an issue. I am going to have an issue with the Rev Adj % calc in my pivot table when I select any date for 2016 on the date slicer. How can I create a calculation that is =divide([Month chosen in slicer]-[Pri Year same month as selected in slicer], [Pri Year same month as selected in slicer].

    The Rev Adj % calc below only works in my pivot table if I select a month with the current year on the date slicer.

    Total Revenue:=SUM([ADJ_AMT])

    Revenue YTD:=CALCULATE([Total Revenue],DATESYTD(D_EDMDATE[CAL_MTH_NM]))

    Revenue Prior Year:=CALCULATE([Revenue YTD],SAMEPERIODLASTYEAR(‘D_EDMDATE'[CAL_MTH_NM]))

    Rev Adj %:=DIVIDE([Revenue YTD]-[Revenue Prior Year],[Revenue Prior Year])

    Hopefully this all made sense 🙂
    Thanks!

    1. I had a brain freeze on the comment above. Please don’t spend time on it. It actually does work I don’t know what I was thinking. You can delete it. I think I had changed the date where clause and that is why it wasn’t working. I fixed it and I can now slice 12/1/2015 and it works fine.

  19. Rob, thank you for this, I was looking for a solution for long hours until I found your post and simply put you are the best! 🙂 Simple formula which does exactly what I need, brilliant.

  20. Since my YTD periods always commence with the first of the year, I added a true/false column to my date table calculated as “InYTD”,IF(Format([Date],”MMDD”)<=Format([FactTable AsOf Date],"MMDD"),TRUE,FALSE). The AsOf date is the max date of the input fact table used. That lets me add one more quick selection filter of Dates[InYTD] = True/false when building other measures, and only gets calculated on refresh of tables.

Leave a Comment or Question