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

AlexisOlson's avatar
AlexisOlson
Super User
7 years ago

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

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    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!

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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!

     

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous,

     

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

     

    Best Regards,
    Dale