Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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.

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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!

17 Replies

  • Matthew_Theis's avatar
    Matthew_Theis
    Advocate II
    4 years ago

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

  • affan's avatar
    affan
    Solution Sage
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi affan,

       

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

       

      Thank you for your help!

       

  • petrovnikitamai's avatar
    petrovnikitamai
    Resolver V
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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!