Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

SAMEPERIODLASTYEAR() conflict with table

Hi,

 

Im encountering a issue that's kind of leaving me head scratching.

 

I have 3 tables:

- [DATE], created from a CALENDARAUTO function

- [SALES], with my net sales (NS) by material and date

- [MATERIAL], with the brand corresponding to all material

 

[DATE] is linked in 1-to-many with [SALES]

[MATERIAL] is linked in 1-to-many with [SALES]

 

When i create a visual matrix with date / material / NS of SAMEPERIODLASTYEAR() - all good

When i create a visual matrix with date / brand / NS - all good

When i create a visual matrix with date / brand / NS of SAMEPERIODLASTYEAR - KO

 

I then get the following error message

Function 'SAMEPERIODLASTYEAR' expects a contiguous selection when the date column comes from a table on the 1-side of a bi-directional relationship.

 

But clearly, the issue isnt from my date table, or conflict would also arise in the visual matrix with date / material / NS of SAMEPERIODLASTYEAR().

 

Any idea?

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , I am assuming you are using date from date table in SAMEPERIODLASTYEAR ('DATE'[DATE])

    and the date in the visual is also coming from the date table.

     

    You can try a year behind the measure

    Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

     

    example SAMEPERIODLASTYEAR
    Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , I am assuming you are using date from date table in SAMEPERIODLASTYEAR ('DATE'[DATE])

    and the date in the visual is also coming from the date table.

     

    You can try a year behind the measure

    Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

     

    example SAMEPERIODLASTYEAR
    Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    EDIT : i seem to actually not be able to create a visual with material / NS of SAMEPERIODLASTYEAR(), but no issue with date / NS of SAMEPERIODLASTYEAR()

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hello Sorry for the late response but I think the solution is just to add a CALCULATETABLE function before the SAMEPERIODLASTYEAR function, please see the example below:

     

    Company Sales ($) PY =
    CALCULATE( [Company Sales ($)] ,
          CALCULATETABLE(
                   SAMEPERIODLASTYEAR( 'Date'[Date] ) 
                                       )
                        )
    Here the table 'Date' is marked as date table and, is related to the Sales table in a 1 to Many relationship.
    With this change you can select non contiguous periods in your report 😄