SAMEPERIODLASTYEAR expects a contiguous selection

Question

Hello all,&nbsp;I have the following measure:&nbsp;Dynamic Capacity SPLY = CALCULATE(SUM('Fact_Schedule'[DynamicAdjustedSlots]),SAMEPERIODLASTYEAR('Date Dim'[Date]))-[DynamicPTO]I'm getting the following error message:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;My date dim is a calendar table and has contiguous unduplicated dates. I'm not sure what the problem is.Here is a screenshot of my table relationships:&nbsp;Any ideas what's wrong?&nbsp;&nbsp;

bsphil · Answer

This is a very late response but I just resolved a near identical issue and felt it should be documented.&nbsp;The problem is actually the bidirectional filter on your Holidays and COVID19_Schedule tables that connect to your Date table, even though those tables aren't necessarily involved in the calculation. Being a one-to-one relationship forces bidirectional filtering, so switch that to be a many-to-one relationship so you can mark the cross filter direction as single.&nbsp;I had a very similar situation with a holiday table connecting to a date table:&nbsp;&nbsp;Measures using SAMEPERIODLASTYEAR failed until I made this minor change, now those measures calculate properly:&nbsp;&nbsp;

datazoe · Answer

I ran into a similar issue. Is your Date Dim a calculated table in Power BI that is getting the start/end dates from another table?

amitchandak · Answer

StephenK , you to remove bi-directional join as that is causing date in date dimension also getting filtered.
Make it only one-directional 1- Many from date to Data table

v-deddai1-msft · Answer

Hi StephenK&nbsp;,&nbsp;In addition to amitchandak's reply, you should&nbsp; make it only one-directional 1- Many from date to Data table. Because bi-directional join as that may cause date in date dimension also getting filtered. if you are trying to use these time intelligence functions like SAMEPERIODLASTYEAR or DATEADD, It needs a contiguous interval in date column.&nbsp;If there is a filter to remove rows from the middle. &nbsp; Same period last year wants to look at just exactly 1 "period" -- that can be a week, day, month, 4.6 weeks... but they need to be 1 continuous range not like "the 1st and 4th week of July" (which would have a gap... in the 2nd and 3rd weeks, and is not allowed).&nbsp;If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.&nbsp;Best Regards,Dedmon Dai

stephenk · Answer

Thanks for the responses everyone.&nbsp;amitchandak&nbsp;v-deddai1-msft&nbsp;i already checked that... Am I missing something obvious?

Forum Discussion

SAMEPERIODLASTYEAR expects a contiguous selection

8 Replies

Related content

Substitute for SAMEPERIODLASTYEAR() which will work for non contiguous selections

Function 'SAMEPERIODLASTYEAR' expects a contiguous selection Issue

What is contiguous selection?

The "SAMEPERIODLASTYEAR" function expects a contiguous selection

Contiguous date selection error - YTD LY