SAMEPERIODLASTYEAR expects a contiguous selection

[StephenK]

Hello all,

I have the following measure: 

Dynamic Capacity SPLY = CALCULATE(SUM('Fact_Schedule'[DynamicAdjustedSlots]),SAMEPERIODLASTYEAR('Date Dim'[Date]))-[DynamicPTO]

I'm getting the following error message: 

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:

Any ideas what's wrong?

[StephenK]

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

[v-deddai1-msft]

Hello @StephenK ,

Could you check the relationship between Fact_Schedule and Date Dim? Must be unidirectional 1- Many.

As a workaround, you can avoid using the time intelligence feature. See https://community.powerbi.com/t5/Desktop/Function-SAMEPERIODLASTYEAR-expects-a-contiguous-selection-...

Best regards

Dedmon Dai

[StephenK]

@v-deddai1-msft I've already checked that. Please see my previous screenshot for verification. I've looked into the workaround you suggested. I'm not finding a good application for it because I need my SAMEPERIODLASTYEAR measure to be visualized by month.

[bsphil]

This is a very late response but I just resolved a near identical issue and felt it should be documented.

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.

I had a very similar situation with a holiday table connecting to a date table:

Measures using SAMEPERIODLASTYEAR failed until I made this minor change, now those measures calculate properly:

[Syndicate_Admin]

Thank you so much for your help! With this I discovered the problem I had in a model that was not doing the calculation for me

[v-deddai1-msft]

Hi @StephenK ,

In addition to amitchandak's reply, you should  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.

If there is a filter to remove rows from the middle.   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).

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[amitchandak]

@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

[DataZoe]

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?