SAMEPERIODLASTYEAR error without any filters or slicers

[unclejemima]

I'm using this formula...

TotalIncomeLastYear = CALCULATE([Income4000'sCalc],SAMEPERIODLASTYEAR('Trandata'[DataDate]))

When I put it onto my report I get the error 

MdxScrip(Model)(50,69) Calculation Error in this measure.  'Trandata'[TotalIncomeLastYear]: Function "SAMEPERIODLASTYEAR" expects a contiguous selection when the date column is not unique, has gaps or it contains time portion.

I did a search and this appears typically when there is slicers on the page...I have no slicers or page level filters so I'm not sure where to go from here...

[v-frfei-msft · Accepted solution]

Hi @unclejemima,

Based on my test, here we need to create a separate date table.

Table = CALENDAR(DATE(2016,01,01),DATE(2019,12,31))

Then create relationship between the Trandata table and the date table, and create the two measures.

Income4000'sCalc = SUM(Trandata[Income])

TotalIncomeLastYear = CALCULATE([Income4000'sCalc],SAMEPERIODLASTYEAR('Table'[Date]))

Finally, we can get the result as below.

For more details, please check the pbix as attached.

https://www.dropbox.com/s/pbrqa4d5423ev80/SAMEPERIODLASTYEAR%20error%20without%20any%20filters%20or%...

Regards,

Frank

[v-frfei-msft]

Hi @unclejemima,

Does that make sense? If so , kindly mark the answer as a solution to close the case, thanks in advance.

Regards,

Frank