Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
matdub40
Helper III
Helper III

The "SAMEPERIODLASTYEAR" function expects a contiguous selection

I have a measure "Evo%" that allows me to calculate the percentage change in sales between years, the formula is:

 

Evo %:=
DIVIDE (
[Euro Gross Amount]
- CALCULATE ( [Euro Gross Amount], SAMEPERIODLASTYEAR ( 'date'[date] ) ),
CALCULATE ( [Euro Gross Amount], SAMEPERIODLASTYEAR ( 'date'[date] ) ),
0
)


My date table has continuous values from 2018 to 2022 with no missing days:

matdub40_0-1630655012881.png

 

When I make my formula it worked perfectly and since yesterday I have as error on my Power BI report:


"Calculation error in measure 'dwh_d_order_sku'[Evo %]: The "SAMEPERIODLASTYEAR" function expects a contiguous selection when the date column comes from a table on side 1 of a bidirectional relationship."

 

I don't understand where this can come from since my dates are continuous. Can anyone help me with this?

1 ACCEPTED SOLUTION
goncalogeraldes
Super User
Super User

I think that the error is due to the nature of the relationship between your date table and dwh_d_order_sku table being set to bidirectional. Try switching it to single direction from the date table to the dwh_d_order_sku table, that is, from the one to the many side! 

 

 

goncalogeraldes_1-1630656774165.png

 

(please note that in this image the cardinality is many to many which is not your case, therefore just focus on the cross filter direction)

 

Hope this answer solves your problem! If you need any additional help please tag me in your reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
Thanks!

Best regards,
Gonçalo Geraldes

View solution in original post

2 REPLIES 2
goncalogeraldes
Super User
Super User

I think that the error is due to the nature of the relationship between your date table and dwh_d_order_sku table being set to bidirectional. Try switching it to single direction from the date table to the dwh_d_order_sku table, that is, from the one to the many side! 

 

 

goncalogeraldes_1-1630656774165.png

 

(please note that in this image the cardinality is many to many which is not your case, therefore just focus on the cross filter direction)

 

Hope this answer solves your problem! If you need any additional help please tag me in your reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
Thanks!

Best regards,
Gonçalo Geraldes

Hello @goncalogeraldes,

 

Thank you for your answer, indeed I had a one to one relationship and in both directions.
I changed to one to many from the date table to dwh_d_order_sku and it works!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.