SAMEPERIODLASTYEAR returns bank

[Anonymous]

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?

[Anonymous]

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]

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]

Is the year date slicer also using the fulldatealternatekey column?

[Anonymous]

For the Slicer I used :

Year = YEAR('Date'[FullDateAlternateKey])

Now that  I replace the slicer with FullDateAlternateKey, it is working perfectly. 

But why ??

[Anonymous · Accepted solution]

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.

[Anonymous]

Yes, now I understand what the problem is. 
Thank you for the explanation. 

I just wanted to have 'year' separately, and by using YEAR(date) function, the return value is an integer which can not be used in the SAMEPERIODLASTYEAR function. 

The only option that I have( or I can think of) is to add a calendar to my slicer like below: 

[v-chuncz-msft]

@Anonymous,

You may add ALL Function.

https://community.powerbi.com/t5/Desktop/Last-Fiscal-Year-Total-by-Month-DAX/m-p/224625#M99894