Tiny Lizard

Power Pivot Consulting and Training

  • About
  • Blog
  • Power Pivot
  • Training
  • Contact

Bug in SAMEPERIODLASTYEAR()?

September 12, 2014 By Scott Senkeresty 1 Comment

We start with two simple measures, that we have written like 900 times before:

TotalSales:=SUM(SalesTable[Dollars])
TotalSales-PY:=CALCULATE([Total Sales],SAMEPERIODLASTYEAR(Calendar[Date]))

image

Drop those bad boys in a pivot table with Year on rows, and it looks HOT!    I briefly thought having a grand total was weird, since “Prior Year’ on a set of years feels slightly odd… but fine.  If you have { 2011, 2012 } then prior year would mean { 2010, 2011 }.  I can dig that.

Feeling bold, we throw quarters under the years… and we are super impressed by how our Prior Year measure continues to work even at different time granularity.

imageBut, we are super curious what happened in Q2 2011-2012.  Sales more than doubled!   We decide to put a slicer on quarters, and click the cut little “2”… and watch the epic explosion of dooooomm!

ERROR – CALCULATION ABORTED: Calculation error in measure ‘SalesTable'[Total Sales – PY]: Function ‘SAMEPERIODLASTYEAR’ only works with contiguous date selections.

Hrm.

Okay, in any individual year… the dates are continuous.  But since we are still blowing up and sad, we can assume (as is so often the case), that weirdness is happening in the grand total cell.   So, we just turn that off.   Only now… things are almost worse.   No errors are showing up, but then… there is no data in my pivot table either. Sad smile

We could try wrapping an IFERROR around it, but… trust me, that is fruitless.  The error is not trapped, but at least that bug is already acknowledged by Microsoft.

I think the cleanest fix for us is to add a check to ensure (or is it insure?  Nah, always go with your first guess) ensure we are looking at just one year.

TotalSales-PY:=IF (ISFILTERED(Calendar[Year]), CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Calendar[Date])))

imageAnd with that measure, we can indeed slice on Quarters.  Hurray!

Now, where is this “supposed bug”, Scott?   Well, it might be a matter of opinion, but I find the behavior a bit inconsistent… versus the much friendlier DATESYTD() function.

With that  function I don’t need any special magic.  If multiple years are given to DATESYTD() it just says “hrm.  multiple years doesn’t make much sense, I think I will return blank”.

Which… is a nicer experience, in my opinion.

Lifetime To Date – Deep Dive
ALL() vs ALLSELECTED()

Filed Under: Power Pivot

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 280 other subscribers

Comments

  1. Marco Russo says

    September 16, 2014 at 12:49 am

    The reason for this behavior is related to another scenario. Imagine you select two non-contiguous months (such as April and June) in slicers and you display the year 2011 and 2012 in rows: you should see the sum of the two months instead of blank. The design today is to raise the error, and I agree that another behavior might be preferrable.
    However, you can implement such a calculation in DAX working also on non-contiguous months. Look at the PY Sales implementation in the Time Patterns: http://www.daxpatterns.com/time-patterns

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Latest Blog Posts

  • The streak is alive! August 10, 2017
  • DAX KEEPFILTERS Plus Bonus Complaining! July 20, 2017
  • VAR: The best thing to happen to DAX since CALCULATE() July 11, 2017
  • Review: Analyzing Data with Power BI and Power Pivot for Excel June 20, 2017
  • Power BI Date Table June 2, 2017

Follow on Twitter

My Tweets

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 280 other subscribers

Copyright © 2022 Tiny Lizard