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

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's avatar
Ashish_Mathur
Super User
7 years ago

Hi,

 

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

  • 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?

         

    • petrovnikitamai's avatar
      petrovnikitamai
      Resolver V
      7 years ago

      check doc by 

       

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