- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Function 'SAMEPERIODLASTYEAR' expects a contiguous selection Issue
Hello,
I have a problem using SAMEPERIODLASTYEAR function to calculate the sales YTD.
Here is my setup:
- I have a databable with date called FACT[InvoiceDate]. Format is date.
- I created a calendar table (Date = CALENDAR (DATE(2016,1,1), today())) with date and year column. Format is date.
- The Dates table has a 1-many relationship with the FACT[InvoiceDate] column.
- I am using below formula to measure YTD:
- Total Sales = SUM(FACT[Sales])
- Total Sales YTD = TOTALYTD(FACT[Total Sales],DATESYTD('Date'[Date]))
- Total Sales YTD Last Year = CALCULATE(FACT[Total Sales],SAMEPERIODLASTYEAR('Date'[Date]))
It works if my relationship between date table and FACT table are both ways but I get "Function 'SAMEPERIODLASTYEAR' expects a contiguous selection" if I filter on a specific shop.
Then I changed to single relationship and the result for the previous year is wrong: Total Sales YTD Last Year gives me the total sales for 2017 instead of same period.
I went through many post related to this topic but cannot find the solution.
Any help is appreciated, thanks in advance.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @v-jiascu-msft,
I am using filter instead as follow:
TOTAL SALES YTD =
CALCULATE (
SUM (FACT[Sales] ),
FILTER (
ALL ( 'Date' ),
YEAR ( 'Date'[Date] ) = YEAR ( TODAY () )
&& 'Date'[Date] <= TODAY ()
)
)
Thanks all for your help!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I had the same issue, but when I changed the filter direction from both to single my problem was solved.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
i do not know what wrong
try to use equivalent formula
DATEADD('Date'[Date],-1,year)
or try to use in Total Sales YTD Last Year SUM(Fact[Sales]) instead link to measureFACT[Total Sales].
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello @petrovnikitamai,
I tried to add new table with DateAdd but the problem is the same.
The Last year to Date take the entire year of sales.
I also tried SUM(Fact[Sales]) instead but not working.
Is there any alternative to this SAMEPERIODLASTYEAR formula?
Thank you very much!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Share the link from where i can download your PBI file.
Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Ashish_Mathur ,
unfortunatelly my BI is using only direct query so you won't see the data.
Perhaps I can do specific screenshot that can help you?
Thank you,
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Your relationship diagram looks weird to me. I would expect a one-to-many relationship from your date table to your fact table, not a many-to-many relationship filtering the other way.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @AlexisOlson,
I try to create one-to-many relationship from my date table to my fact table but always get this message:
"The cardinality you selected is not valid for this relationship".
My date calendar is using this formula:
dates = DATEADD('Date'[Date],0,year)
Is it something else I can try?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try deleting and recreating the relationship. The dates table should not have multiple rows for the same date.
If your dates table has duplicate dates, then something is likely wrong with it.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I deleted and re-created the link but same issue.
I also created a new date table : Date = CALENDAR(DATE(2016,1,1), today()), same issue with the relationship
In my date table, there is no duplication and I am using format Date.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Anonymous,
The relationship could be the root cause. Can you change it to 1:* manually? Please also check if there are any duplicates. Please refer to the snapshot below.
The SAMEPERIODLASTYEAR works now after the release of the Composite model.
Best Regards,
Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @v-jiascu-msft,
Thank you for the explaination.
I finally found another way to calculate sale year to date using filter instead of SAMEPERIODLASTYEAR.
It is working quite fine now.
Thank you!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Anonymous,
I'm glad you solved it. Can you share the solution, please? Please also close this thread.
Best Regards,
Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @v-jiascu-msft,
I am using filter instead as follow:
TOTAL SALES YTD =
CALCULATE (
SUM (FACT[Sales] ),
FILTER (
ALL ( 'Date' ),
YEAR ( 'Date'[Date] ) = YEAR ( TODAY () )
&& 'Date'[Date] <= TODAY ()
)
)
Thanks all for your help!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @v-jiascu-msft,
Thank you for the explaination.
I finally found another way to calculate sale year to date using filter instead of SAMEPERIODLASTYEAR.
It is working quite fine now.
Thank you!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Anonymous
Is it possible to share the pbix file? If not, please share a snapshot of the relationships view in you file
Regards
Affan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @affan,
here is the screen shot of both tables. I set a single direction.
Thank you for your help!

Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
09-02-2024 10:49 PM | |||
05-16-2024 07:52 AM | |||
09-10-2024 07:37 AM | |||
08-30-2023 12:34 PM | |||
12-11-2023 07:54 AM |
User | Count |
---|---|
90 | |
82 | |
57 | |
41 | |
39 |