cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
webportal Member
Member

SAMEPERIODLASTYEAR with filter return error message

Hello,

 

My data model has a column "CompanyName" and a series of accounting transactions.

I have 2 measures:

  • Sales = CALCULATE(SUM('Table'[Balance]); 'Table'[AccountID]=71)
  • Sales LY = CALCULATE(Sales; SAMEPERIODLASTYEAR('Calendar'[Date]))

They both work well, but the report page contains a slicer with the field "CompanyName".

 

When the slicer is marked (ie the only company in the model is selected), the function SAMEPERIODLASTYEAR breaks and we get the error:

 

MdxScript(Model) (110,61) Error in measure Sales LY: SAMEPERIODLASTYEAR requires a contiguous selection when the date column is in one side of a bidirectional relationship.

This doesn't make sense to me, because when the slicer selects the only company in the model, it should be selecting all the rows in the "Sales" table. I have the calendar table "Calendar" marked as Date.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
magsod Member
Member

Re: SAMEPERIODLASTYEAR with filter return error message

Do you have the relationship between Sales and Calendar as a bidirectional relationship?

If so you should change that to a single direction relationship.

 

Br,

Magnus

2 REPLIES 2
Highlighted
magsod Member
Member

Re: SAMEPERIODLASTYEAR with filter return error message

Do you have the relationship between Sales and Calendar as a bidirectional relationship?

If so you should change that to a single direction relationship.

 

Br,

Magnus

webportal Member
Member

Re: SAMEPERIODLASTYEAR with filter return error message

That solved it! 

:-)

 

It turns out the cardinality of this relationship was 1 to 1 and bidirectional.

Changing it to many to one and unidirectional solved the problem!

 

But why is this?

 

Thank you for your help!