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!

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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,

 

AlexisOlson's avatar
AlexisOlson
Super User
7 years ago

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

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