SAMEPERIODLASTYEAR does not work as expected

[rickylee]

Hello,

I am trying to use the SAMEPERIODLASTYEAR formula but it seems it does not work as it is. 

Last year revenue2a = CALCULATE(sum('Revenue 2017 18'[Total]),SAMEPERIODLASTYEAR('Calendar'[Date]))

The relationship between 'Calendar' table and 'Revenue 2017 18' can be seen below:

The calendar table has one formula only: 
Calendar = CALENDARAUTO(3)

The 'revenue 2017 18' table has 12 rows of data only:

When I try to display the thidd column which has the formula:
"Last year revenue2a = CALCULATE(sum('Revenue 2017 18'[Total]),SAMEPERIODLASTYEAR('Calendar'[Date]))"
The column displays data in 2019 which I don't have such data at all. 

Did I do anything wrong?

Thanks,
Ricky

[rickylee]

@v-danhe-msft

Hello, it does not work for me as such but  I manually created an alternative solution. It does not use the function which is not ideal.

Thanks,
Ricky

[v-danhe-msft]

Hi @rickylee,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[rickylee]

*I have posted the following before but it did not exist in my subscription. So I re-post again.

Hello,

I am trying to use the SAMEPERIODLASTYEAR formula but it seems it does not work as it is. 

Last year revenue2a = CALCULATE(sum('Revenue 2017 18'[Total]),SAMEPERIODLASTYEAR('Calendar'[Date]))

The relationship between 'Calendar' table and 'Revenue 2017 18' can be seen below:

The calendar table has one formula only: 
Calendar = CALENDARAUTO(3)

The 'revenue 2017 18' table has 12 rows of data only:

When I try to display the thidd column which has the formula:
"Last year revenue2a = CALCULATE(sum('Revenue 2017 18'[Total]),SAMEPERIODLASTYEAR('Calendar'[Date]))"
The column displays data in 2019 which I don't have such data at all. 

Did I do anything wrong?

Thanks,
Ricky

[v-danhe-msft]

Hi @rickylee,

Could you have received the reply I sent to you before? And have you tetsed to modify your calendar table like this?

Table = CALENDAR("2017/1/1","2018/12/31")

Regards,

Daniel He

[v-danhe-msft]

Hi @rickylee,

Based on my test, this is due to your calendar table contains the date in 2019, you could modify your calendar table like this:

Table = CALENDAR("2017/1/1","2018/12/31")

And now you could get the correct result:

You can also download the PBIX file to have a view.

 https://www.dropbox.com/s/jiyrv4foti2m85a/SAMEPERIODLASTYEAR%20does%20not%20work%20as%20expected.pbi...

Regards,

Daniel He