Function 'SAMEPERIODLASTYEAR' expects a contiguous selection Issue

[Anonymous]

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.

[Matthew_Theis]

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

[petrovnikitamai]

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

[Anonymous]

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!

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Anonymous]

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,

[petrovnikitamai]

check doc by 

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

[AlexisOlson]

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.

[Anonymous]

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?

[AlexisOlson]

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.

[Anonymous]

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.

[v-jiascu-msft]

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

[Anonymous]

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!

[v-jiascu-msft]

Hi @Anonymous,

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

Best Regards,
Dale

[Anonymous · Accepted solution]

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!

[Anonymous]

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]

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

[Anonymous]

Hi @affan,

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

Thank you for your help!