Reply
avatar user
Anonymous
Not applicable

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.

1 ACCEPTED SOLUTION
avatar user
Anonymous
Not applicable

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!

View solution in original post

17 REPLIES 17
Matthew_Theis
Advocate II
Advocate II

I had the same issue, but when I changed the filter direction from both to single my problem was solved.

petrovnikitamai
Resolver V
Resolver V

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].

 

avatar user
Anonymous
Not applicable

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!

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/
avatar user
Anonymous
Not applicable

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,

 

check doc by 

image.png

 

This DAX function (Sameperiodlastyear) is not supported for use in DirectQuery mode. For more information about limitations in DirectQuery model, see

 

 

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.

avatar user
Anonymous
Not applicable

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?

 

PowerBI relationship2.jpg

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.

avatar user
Anonymous
Not applicable

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.

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. 

Function-SAMEPERIODLASTYEAR-expects-a-contiguous-selection-Issue

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
avatar user
Anonymous
Not applicable

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!

 

 

 

Hi @Anonymous,

 

I'm glad you solved it. Can you share the solution, please? Please also close this thread.

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
avatar user
Anonymous
Not applicable

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!

avatar user
Anonymous
Not applicable

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!

 

 

 

affan
Solution Sage
Solution Sage

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

avatar user
Anonymous
Not applicable

Hi @affan,

 

here is the screen shot of both tables. I set a single direction.

 

Thank you for your help!

 

PowerBI relationship.jpg

avatar user

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!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)