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.
Solved! Go to Solution.
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.
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.
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.
How about this instead of using SAMEPERIODLASTYEAR:
Fees Billed SPLY = CALCULATE([Fees Billed YTD],DATEADD(Table_BasicCalendarUS[DateKey],-1,YEAR))
I don't believe so or that it's necessary. I just always make sure my PK is an actual date column and not a key like 20170401 or something like that, as I've found many of the DAX capabilities won't work correctly if you don't use a date column when relating tables.
The only other thing I can think of for you to check is to see if there are other roundabout relationships between the date table and another table that then joins back to your fact table directly or indirectly. If there's any bi-directional relationship there, it might affect what you're trying to do even though your direct relationship between date and fact table is single...
I had the same issue.
I had another table with an inactive relationship and still couldn't get a month over month view.
I only had it solved after delete the inactive relationship between the date table and other table.
I had this issue and changing the sameperiodlastyear from my year-month column to the full date column as suggested solved the issue, thanks!
I had same issue before and I found out that as @bblais mentioned, I had two tables havng relationship with dates table. Once I set corss filter direction single for both of them. problem was resolved.
Check out new user group experience and if you are a leader please create your group!
On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks
Check out how to claim yours today!