SAMEPERIODLASTYEAR issue

[Anonymous]

Hi,

Having an issue whereby using SAMEPERIODLASTYEAR it's not recognising a date column, even though it's definitely formatted that way.

DAX...

PGT Previous = if([PGT Int Pop] = BLANK(), BLANK(), [PGT Int Pop] - CALCULATE([PGT Int Pop], SAMEPERIODLASTYEAR('Academic Year Conversion'[Academic Year].[Year])))

Coming back with the following error...

Then looking at the Academic Year Conversion column, this is definitely formatted as DATE..

...same in QueryEditor...

Any idea where I'm going wrong?

[Anonymous]

Using the auto-generated date/time hierarchies is one of the worst things you can do in Power BI. Please never do this. Stick to a calendar that you either import or create in Power Query. You'll then always be safe and will reduce the model size as well.

[amitchandak]

@Anonymous , remove .year. But better use a date table

PGT Previous = if([PGT Int Pop] = BLANK(), BLANK(), [PGT Int Pop] - CALCULATE([PGT Int Pop], SAMEPERIODLASTYEAR('Academic Year Conversion'[Academic Year])))

PGT Previous = if([PGT Int Pop] = BLANK(), BLANK(), [PGT Int Pop] - CALCULATE([PGT Int Pop], SAMEPERIODLASTYEAR('Date'[Date])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.