Wrong Total from SAMEPERIODLASTYEAR metric in table

[Anonymous]

Hi!

I've built a simple table with Date (10 rows, one per day), CY Impressions, LY Impressions and % Delta CY vs LY. 

The figures I get for every day are right, but when I include the last available day I have data available for in CY, the Total of LY Impressions at the bottom is completely wrong.

I am filtering the table with Date slicer (if that helps), and I only get the correct LY Total amount when only one day is in the table. But as I request two or more days, the LY Total is wrong...

The DAX I used to create the Measures: 

CY Impressions = sum(searchanalytics_query[Impressions])

LY Impressions = CALCULATE(SUM([Impressions]),SAMEPERIODLASTYEAR('Date'[Date]))

Any idea around this?

[themistoklis]

@Anonymous

Try to put ALLEXCEPTand see if it works

LY Impressions = CALCULATE(SUM([Impressions]), SAMEPERIODLASTYEAR('Date'[Date]), AllExcept('Date', 'Date'[Date]))

[Anonymous]

Thanks @themistoklis, but unfortunately it didn't work.

I wish I could upload images here, but this tool doesn't seem to work very well...

[themistoklis]

@Anonymous

Clear the cache of your browser and then you will be able to upload the images.

Also if you could share the file with us that would be great.

Mask any sensitive data

[Anonymous · Accepted solution]

Thanks!

I finally made it: my dates table was based off this measure: 

Date = ALLNOBLANKROW ( searchanalytics_query[Date] )

and I've changed it by: 

Date = CALENDARAUTO()

It seems to work ok now. 

Thanks for your help anyways, appreciate it.