Best way to add YEAR from date column

[ilcaa72]

i have a Sales table that has dates in a column, 4/15/2015, etc.  I just wanted to show the Years. I thought I could just make a new measure and use a formula similiar to this...

years = YEAR('Sales Table' [dates])

i dont even get the table/column names with Intellisense... why cant I do something like this in PBI?  Whats the proper way?  a short explanation would be appreciated.

[Anonymous · Accepted solution]

Hi @ilcaa72,

YEAR function expect a single date in datetime or text format, containing the year you want to find. 
You can get:

YEAR(LASTDATE('Sales Table' [dates]))

or

YEAR(FIRSTDATE(Calendar[Date]))

To obtain the list of years you can mark the field as Date Hierarchy and select only the years

Regards.

[TomMartens · Accepted solution]

Hey,

if you create a calculated column in your sales table you can use the function you already have. The benefit of creating a calculated column over a measure is that you can use the calculated column as a slicer. The price you pay, is an additional memory footprint, depending on the number of rows in your sales table.

I would recomment that you consider to create a separate date table and relate this table with your sales table. Here you will find some date related calculations that heavily rely on the usage of a separate date table: https://www.daxpatterns.com/time-patterns/

By the way, intellisense recognizes that the context expects an aggregate function, for this reason it does suggest a tablename. This behavior often reminds me that I should check my syntax and the context 🙂

Hope this what you are looking for

Regards

Tom

[Anonymous · Accepted solution]

Hi @ilcaa72,

YEAR function expect a single date in datetime or text format, containing the year you want to find. 
You can get:

YEAR(LASTDATE('Sales Table' [dates]))

or

YEAR(FIRSTDATE(Calendar[Date]))

To obtain the list of years you can mark the field as Date Hierarchy and select only the years

Regards.