Get the YTD of same period last year using DAX

You have followed the time intelligence golden rules to perfection and you think you can handle all time intelligence DAX functions. But unlike the contoso dataset you are working with actual live data from your own production environment and here you have data until the current month not the end of the year. Like this:

We want to compare the YTD from the current year to the YTD of the previous year to the current period last year. First we select the YTD of the current year by selecting the current year in the slicer and using the normal sum. Now to get the YTD of previous year we do a:

=TOTALYTD(sum(Table1[sales]),
                 DATEADD(datum[Date],-12,MONTH))

What happens here? We do the sum of table1[sales] where we use the value of datum[Date] to calculate the sum of sales one year back. What is the result:

This is not what we expected this is the sales of the entire previous year. It is much more. Why? Well one of the golden rules is that you need to have a complete contiguous time table, that means that in my seperate time table we have loaded dates until the end of the current year. And what does TOTALYTD do? According to BOL : “Evaluates the year-to-date value of the expression in the current context.” The current context is “2010” and the lastdate of our time table is 12/31/2010. Not the 6/1/2010 that is the actual last date we had data.

So here we are in a deadlock between the time intellegence golden rules and what we want. When we would use the date from the fact table it would give us the correct last date but the time intellegence functions wouldn’t return results because we wouldn’t use our seperate related time table  (read here why).

So what can we do? Well we could make the formula ourself. In our case we want the sum of sales from the same period last year, that means from 1/1/2009 to  6/1/2009. We can use the DAX function DATESBETWEEN to  create a custom date range. We want to get the range of datum[Date] values from the first day of the previous year:

FIRSTDATE(DATEADD(datum[Date],-12,MONTH))

We use the datum[Date] column here because we know this is a coninues data range and it would always contain 1/1. Using dateadd we go back 12 months and firstdate makes sure we get the first value from this range. To select the date we want the calculation to end we use:

LASTDATE(DATEADD(Table1[Date],-12,MONTH))

Here we use the lastdate of the fact table to determine the same date one year back, in our case 6/1/2009.

The total formula would look like:

=CALCULATE(sum(Table1[sales]),
                  DATESBETWEEN(datum[Date],
                                  FIRSTDATE(DATEADD(datum[Date],-12,MONTH)),
                                  LASTDATE(DATEADD(Table1[Date],-12,MONTH))
                  ))

PowerPivot DAX will calculate the sum of  Table1[sales] from a date range between 1/1/2009 and 6/1/2009.

Which gives us the result we want:

  • Michiel Rozema

    I often use these measures:
    [SalesYTD] = TOTALYTD(SUM(Table1[sales]),Date[Date])
    [SalesYTDLastYear] = ([SalesYTD])(SAMEPERIODLASTYEAR(Date[Date]))

    Do these measures suffer from the same behaviour? If so, this is something you should fix in the next version :-). This is a tough issue to explain to the average user.

  • Kasper de Jonge

    Hi @Michiel Rozema ,

    Yes they behave exactly the same:
    TOTALYTD “Evaluates the year-to-date value of the expression in the current context.”
    and
    SAMEPERIODLASTYEAR: “Returns a table that contains a column of dates shifted one year back in time from the dates in the specified dates column, in the current context.”

    Both use the context to determine their range. I don’t really know if this is a bug, it is clearly in the definition. I think this is a though one.

  • Hi. This tip is HUGE to me, so thank you. I will attempt to describe what i am using it for and perhaps someone can tell me if there is yet a better way.

    I need to get a MTD of the NEXT month(which will not be a complete month) and compare to the same range of dates in the prior years month. this is the formula I used. The thing is, i have two different places to get the DATE from. One is the FACT table (DATA) and the other is from my DATETABLE (POSTEDDATE).

    Is there a better way of handling this?

    CALCULATE(SUM(Data[volume]),DATESBETWEEN(PostedDate[PostedDate],CALCULATE(DATEADD(firstdate(Data[PostedMonth]),-12,month),NEXTMONTH(PostedDate[PostedDate]),PostedDate[PostedWorkDay]=1,Source[SourceID]=2),CALCULATE(DATEADD(lastdate(Data[PostedMonth]),-12,month),NEXTMONTH(PostedDate[PostedDate]),PostedDate[PostedWorkDay]=1,Source[SourceID]=2)))

    • Kasper de Jonge

      Hi Shawn,

      I don’t see a different way right now. There might be a different way but is there a reason you want to have a different query ?

      Thanks,
      Kasper

  • Patricio

    I have the same issue. I work with a monthly report, I have a pivot table with sales, YTD, MAT, change over previous year and % change previuos year.
    So It look more or less like these.
    Radetiketter Liters Liters ifjor Endring Endring % YTD MAT
    2008 923 172 945 465 -22 293 -2,36% 923 172 923 172
    2009 939 544 923 172 16 372 1,77% 939 544 939 544
    2010 981 960 939 544 42 416 4,51% 981 960 981 960
    2011 697 135 981 960 -284 825 -29,01% 697 135 697 135
    januar 61 807 56 796 5 010 8,82% 61 807 986 971
    februar 70 080 60 644 9 436 15,56% 131 887 996 407
    mars 83 721 70 610 13 111 18,57% 215 608 1 009 518
    april 62 558 65 893 -3 335 -5,06% 278 165 1 006 183
    mai 97 843 76 553 21 290 27,81% 376 009 1 027 473
    juni 95 785 107 525 -11 739 -10,92% 471 794 1 015 734
    juli 60 932 70 639 -9 707 -13,74% 532 726 1 006 027
    august 80 424 82 145 -1 721 -2,10% 613 150 1 004 306
    september 83 985 83 913 71 0,09% 697 135 1 004 377
    oktober 77 165 -77 165 -100,00% 697 135 927 212
    november 131 932 -131 932 -100,00% 697 135 795 280
    desember 98 145 -98 145 -100,00% 697 135 697 135

    But look what happend with oktober, november desembre… THEY APPEAR in the report, this should not occur as destroy the purpose of the report (see the results for 2011). I tried your fix but did not work, it creates another colum with the YTD of 2011, but appears from 2010 and down not in 2011.
    I guess the only way to fix it is to deleted from the tables dates the months with sales (i.e oktober and so on)… clearly not the best solution. Any help will be highly appreciated.

  • David

    These are great tips, but is there a way to calculate SamePeriodLastMonth instead of SamePeriodLastYear? At my company, we do month to month revenue differences and variances.

    I’ve already tried the following formulas, which return blank values.
    =calculate(sum(SalesOrderDetail[linetotal]),PARALLELPERIOD(SalesOrderHeader[Orderdate],-1,month))

    –this formula also returns a blank column!
    =calculate(sum(SalesOrderDetail[linetotal]),DateADD(SalesOrderHeader[Orderdate],-1,month))

    Please assist.

  • Kasper de Jonge

    Hi David,

    I would recommend checking my time intelligence post:
    https://www.kasperonbi.com/powerpivot-ctp3-what-is-new-for-time-intelligence-functions
    and create a date column and use that in your functions. That will return values.

    Kasper

  • Fabrizio

    Hi Kasper,
    I tryed to use integrated time intelligence on our tabular DB, unfortunately our calendar is based on 364 days (52 weeks x 7) .
    I have a field with “year / week of year / day of week” but I cannot figure out a solution to obtain the translated set just substracting one thousand.
    Any suggestion?
    Thank you very much
    Fabrizio

  • Derek

    Hello Kasper,

    I am not sure whether or not I understand this correctly. Using your method, it seems a little imperfect.
    For example, today is June 4th. And the last date in fact table is “May 28”, which possibly means no data in the last 6 days for this year. However, in the previous year, there are some data between May 28, 2011 to June 4th, 2011. BY your way, seems to me it would not count this 6 days into previous year YTD. Is this true?

    Any possiblity to use the function “Today()”, which apprently can not apply into dateadd, but it might mae more sense to YTD concept.

  • Carsten

    Great Blog!

    I tried your ‘final’ formula but can not get it to work. I am a Beginner and just trying to understand DAX.

    My formula:

    =CALCULATE
    (sum(FactActuals[Net Act Sales]),
    DATESBETWEEN(DimDate[Datekey],
    FIRSTDATE(DATEADD(DimDate[Datekey],-12,MONTH)),
    LASTDATE(DATEADD(FactActuals[Period],-12,MONTH))
    ))

    This is the error message:

    The function SUM takes an argument that evaluates to numbers or dates and cannot work with values of type String.
    Measure group ‘FactTarget’ must have at least one partition defined in Tabular mode.

    Any insights what is going wrong?

  • Carsten

    Me again 😉

    Not sure what happened but I slept one night, re-typed the above formula and voila it works. However I have the following problem.

    Let’s say I have Sales Data for years 2010, 2011 and 2012. Works like a charm for 2011 and 2012 but when selecting 2010 it shows under Grand Total the Total Sales of all available periods. What can be done to have BLANK/ZERO shown under Grand Total when there is no data for the PY time period?

    Thx again for help.

    • Fabio pereira lopes

      You managed to solve the problem, I have an equal.

  • khaoula

    My project is to work a dashbord of management of the imputations in a company(society). I worked the KPI.
    My maintaining spot is to work a dashbord (a curve of tendences) which illustrates these KPI over 12 weeks;
    I cant found a formula haw to get the last 12 weeks from a variable date
    please i need ur help kasper

  • khaoula

    hi,
    for more explication, in my table FACT_IMPUTATION , i have the calculated KPI [rate of imputation],i used to get it until the 12 weeks from a current date, or a variable date from a slicer date

  • Great article!
    I think even better is to: put DATEADD() out of FIRSTDATE() / LASTDATE()

  • ALICE BOTTERI

    I used the formula and it works, but in my first year of the fact table, where there aren’t previous year and any dates, I found the value corrisponding the total sum of all year, instead 0 or blank value. What is going wrong?

    • Fabio pereira lopes

      Hi! Alice Botteri,

      You managed to solve the problem, I have an equal.

  • Kiran Raavi

    how to compare yoy sales in qlik view using set analysis