none
Using PARALLELPERIOD producing unexpected results when doing YOY comparison in DAX

    Question

  • I am trying to generate a year over year comparison by month for a particular measure to calculate the difference/change using PARALLELPERIOD and I am not getting the results that I would expect.   (This is in a tabular model, SQL 2012 SP1).  I have a date dimension tagged as date table with date, month, year, quarter, etc. I have a column called "FullDate" for datetime which is the 1st day of the month so for example "2013-01-01" for January, since we only look at monthly results as the smallest grain for our results.   I have the following calculations:

    Measure:=Sum(Fact[Measure of Thing])

    Previous year Measure:=CALCULATE(Measure,PARALLELPERIOD(Date[FullDate],-1,YEAR))

    YOY Difference:=[Measure]-[Previous year measure]

    NOTE - at this point I am just checking the math, not calculating a percent change yet

    This is what I am trying to get at in the Pivot table,

    Month  2012   2013   YOY Difference

    1          .84     .85       .01

    2          .89     .95       .10

    3          .76     .80       .04

    Instead I get a value of "9" for the YOY difference in the pivot for every month.  How can I use Parallelperiod to get the expected results, or is there another function I should be using for this calculation?

     

     

     

    Tuesday, July 16, 2013 9:46 PM

Answers

All replies

  • Hi

    It seems to be correct, can you try using SAMEPERIODLASTYEAR() as discussed here

    http://bipassion.wordpress.com/2012/08/26/dax-sameperiodlastyear-and-datesytd/


    Prav



    • Edited by Prav_SQL Wednesday, July 17, 2013 6:10 AM Updated comments
    • Proposed as answer by BillAnton Wednesday, July 17, 2013 6:36 PM
    • Marked as answer by N. Edmonds Friday, July 19, 2013 3:27 PM
    Wednesday, July 17, 2013 6:08 AM
  • HI Prav -

    I tried the same calculation with sameperiodlastyear and I get the following error in Excel:

    ERROR -   CALCULATION ABORTED: Calculation error in measure 'Fact'[Previous Year Measure]:   Function 'SAMEPERIODLASTYEAR' only works with contiguous date selections.
    Wednesday, July 17, 2013 3:42 PM
  • Prav is correct...you need to use SamePeriodLastYear()...parallelperiod() is only showing the aggregate for the prior year, regardless of the month-context.

    regarding the error you posted, it seems to be a bug/feature:
    http://cwebbbi.wordpress.com/2010/06/03/the-%e2%80%98function-xxxx-only-works-with-contiguous-date-selections%e2%80%99-error/


    BI Developer and lover of data (Blog | Twitter)

    • Marked as answer by N. Edmonds Friday, July 19, 2013 3:28 PM
    Wednesday, July 17, 2013 6:36 PM
  • So I did modify my datetime table to have contiguous dates, which now means SAMEPERIODLASTYEAR produces no error, which is good, but with semi-unexpected results.  The challenge now is to key off the fiscal date, not calendar date.  (Fiscal Year runs July->Jun) I have projected values loaded up through June of next year, for example, but the calc doesn't pick up April value fiscal year 2013 as compared to April value fiscal year 2014.  I've seen a few blog posts around about issues with fiscal time and how to resolve, them, but this largely with setting the start/end point of the year and solving for YTD scenarios.  Here I just need Value fiscal year 2013 as compared to value 2014. 
    Wednesday, July 17, 2013 10:00 PM
  • Update - With some additional table manipulation I was able to get SAMPERIODLASTYEAR to work for this scenario, thanks for your help Prav!
    Friday, July 19, 2013 3:27 PM