PowerPivot SAMEPERIODLASTYEAR Error

[jcgilbertson]

I am trying to create a year over year comparison of quarterly revenue based on a single table pulling from SQL. When I try to add a calculated column with =SAMEPERIODLASTYEAR([InvoiceDate]) I receive a "#ERROR" error. Here is the error detail:

Column 'InvoiceDate' cannot be found or may not be used in this expression.

If anyone has thoughts on why this might be they would be appreciated.

 

[akice]

Keep in mind that the Dax Time Intelligence functions work by shifting the dates visible in the current filter context, not be performing math on the [InvoiceDate] column. Plus SAMEPERIODLASTYEAR returns a single column table of values, not an individual scalar value which is why you are getting the error.

Power Pivot is an aggregation and summarizing tool so you don't want to create a calculated column in this situation anyway, but rather a measure. Also since you want to use a Time Intelligence function a for sure best practice is to have a separate Calendar table that is related to this table via the [InvoiceDate] field. If you are new to Power Pivot/Dax there is most definitely a learning curve to all this but worth it in my opinion.
I know this post is not too helpful, but way to much to cover here. If you post some sample data and what you want the end result to look like someone can help which may get you started.

 

[jcgilbertson]

Thanks for your help. I'm very new but seems like it will be quite useful so I'll work on the measure approach.