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

SAMERPERIODLASTYEAR - Non Contiguous Date Selection Error

I've read multiple posts on this error: "SAMEPERIODLASTYEAR expects a contiguous selection when the date column comes from a table on the 1-side of a bidirectional relationship." I am still baffled by my error.

 

I have a simple measure for calculating fees billed:

Fees Billed = CALCULATE(SUM(FactTable[AmtBilledForSubtype]),FactTable[BillSubtype]="FEE")

I have another measure for YTD Fees:

Fees Billed YTD = TOTALYTD([Fees Billed],Table_BasicCalendarUS[DateKey])

No problems with either of these measures.

 

Now I want Fees Billed for the same period last year. Here is the troublesome measure:

Fees Billed SPLY = CALCULATE([Fees Billed YTD],SAMEPERIODLASTYEAR(Table_BasicCalendarUS[DateKey]))

 

The error message suggests that there is a bi-directional relationship at the heart of the problem, but 

in my date table, the DateKey column has a one-to-many, single cross-filter relationship to the DateBilled colum in the FactTable.

 

Any suggestions?

1 ACCEPTED SOLUTION

Accepted Solutions
MojoGene Member
Member

Re: SAMERPERIODLASTYEAR - Non Contiguous Date Selection Error

I get the same exact error message. 

14 REPLIES 14
bblais Member
Member

Re: SAMERPERIODLASTYEAR - Non Contiguous Date Selection Error

Just curious, is your Table_BasicCalendarUS[DateKey]) of type date or something else?

MojoGene Member
Member

Re: SAMERPERIODLASTYEAR - Non Contiguous Date Selection Error

It is definitely a date table.

bblais Member
Member

Re: SAMERPERIODLASTYEAR - Non Contiguous Date Selection Error

I mean the DateKey column specifically?

MojoGene Member
Member

Re: SAMERPERIODLASTYEAR - Non Contiguous Date Selection Error

Yes, I should have been more specific. The DateKey column is type date.

bblais Member
Member

Re: SAMERPERIODLASTYEAR - Non Contiguous Date Selection Error

Ok, I'm thinking it's something to do with the fact that your Fees Billed measure is filtering by BillSubType.  So what's happening is that it's coming up with blanks for some of the past year's dates when it's trying to calculate the Fees Billed and it doesn't like it.

 

For the heck of it, can you try adding +0 to your Fees Billed formula:

 

Fees Billed = CALCULATE(SUM(FactTable[AmtBilledForSubtype])+0,FactTable[BillSubtype]="FEE")

I know this won't fix the issue necessarily, but it would confirm what's going on.

MojoGene Member
Member

Re: SAMERPERIODLASTYEAR - Non Contiguous Date Selection Error

I tried the suggestion, but it had no apparent effect on any of the 3 measures.

bblais Member
Member

Re: SAMERPERIODLASTYEAR - Non Contiguous Date Selection Error

Are you using any filters or slicers on the date in your report?  If so, make sure they are not using the date column out of the fact table, but instead the date column out of the date table.

MojoGene Member
Member

Re: SAMERPERIODLASTYEAR - Non Contiguous Date Selection Error

I was slicing the data on the page so that the DateBilled year included only the last 3 years, but one of the first things that occurred to me was to remove this slicer. Removing it had no effect on the error.

bblais Member
Member

Re: SAMERPERIODLASTYEAR - Non Contiguous Date Selection Error

How about this instead of using SAMEPERIODLASTYEAR:

 

Fees Billed SPLY = CALCULATE([Fees Billed YTD],DATEADD(Table_BasicCalendarUS[DateKey],-1,YEAR))