Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

SAMEPERIODLASTYEAR returns bank

Hello all, 

I am working on the AdventureWorks tables and trying to create a Sale Performance report. 

I have these three measures: 

 

Total Sale = SUMX(InternetSales, InternetSales[OrderQuantity]* RELATED('Product'[ListPrice]))
Total Cost = SUMX(InternetSales, InternetSales[OrderQuantity]*RELATED('Product'[StandardCost]))

And 

 

Total Profit = [Total Sale]- [Total Cost]

I want to make Total Profit LY and Total Sale LY, but it returns blank :

Total Profit LY = CALCULATE([Total Profit], SAMEPERIODLASTYEAR('Date'[FullDateAlternateKey]))

 

 

 

 

The date starts from 2011, so it is clear why the SAMEPERIODLASTYEAR() returns blank for 2011, but it shouldn't return blank for the other year, i.e. 2012, 2013 and 2014. 

 

 

I don't understand what is wrong?

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi

     

    It's because your Slicer is using a calculated column and your measure for Same period last year is using another column.

    The sameperiodlastyear function must use the same column that you are filtering with your slicer.

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi elahemeydani

     

    Are you sure that the date column that you choose in the year is the same as [FullDateAlternateKey]?

    If so can you perhaps provide a sample of the file?

     

    Alternatively you can try this small workaround:

    Profit SPLY :=
    VAR MinLY =
    EDATE ( MIN ( Date'[FullDateAlternateKey]' ); -12 )
    VAR MaxLY =
    EDATE ( MAX ( Date'[FullDateAlternateKey]' ); -12 )

    RETURN
    CALCULATE([Total Profit] ; FILTER(ALL('Date') ; 'Date'[FullDateAlternateKey]' <= MaxLY && Date'[FullDateAlternateKey]' >= MinLY))

    Best Regards
    KMW

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you for answering so quickly. 

       

      W

      hen I select no YEAR from the Slicer, it works just perfectly, but when a YEAR is selected, the LY values are blank  

      Below you can see a screenshot of the Date table : 

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago
        Is the year date slicer also using the fulldatealternatekey column?