Contiguous Date Selections SAMEPERIODLASTYEAR

0
votes

I am stuck on a very - I think - simple issue that is somehow now working for me anymore. I have created a measure to count the amount of retails made over the past years (starting in 2014):

Retails := CALCULATE(COUNT(fData[ProductionNumber);fData[OrderStatus]=50))

Next to this our data model consists out of a calendar table ranging from the first of January 2014 up to the 31st of December 2018.

To make a year over year comparison I have defined the 'Previous Year retails' as:

PY Retails := CALCULATE([Retails];SAMEPERIODLASTYEAR('Date'[DateKey])

Somehow this lands me at the 'contiguous date selections'-error and I am unable to see why or how! Using this method to calculate the PY sales has always worked. Somehow in this case it doesn't. I have checked in my fact table and every date is contained in the calendar datekey. I was thinking that this maybe has to do with the fact that there was one retail on the 29th of February 2016 and then when shifting back it doesn't find that same date in 2015?

Thank you for your help!

1
excelpowerbipowerpivotdax

1 Answers

1
votes

You are right to be confused - this is a pretty dumb weakness of the SAMEPERIODLASTYEAR function. To avoid it, I usually wrap it in an IF ... SELECTEDVALUE test to avoid spanning multiple years, e.g. 

PY Retails := IF ( SELECTEDVALUE ( 'Date'[Year]; 0 ) = 0 ; BLANK() ;  CALCULATE([Retails];SAMEPERIODLASTYEAR('Date'[DateKey]) )