Simplest thing in the world

[Stroop]

New to Bi. Just playing about. Thought I was doing the simplest thing in the world but apparently it's not.

I have just 1 table for simplicity. In my data there is a timestamp field. I wanted to create month and year columns based on this timestamp. How hard could it be?

I go to create a customer variable.

In the new column name box I put "year" because that seems a sensible name.

In the customn column formula I put = year([time])

BI then tells me there is no syntax errors have been detected. great stuff. should be no issues then. Oh wait. I click ok and it tells me that "Expression.Error: The name 'year' wasn't recognized.  Make sure it's spelled correctly."

Apparently just finding information related to doing this type of query is impossible too so if anyone can just tell me how I can do this that'd be great. 

[v-sihou-msft]

@Stroop

The custom column you add in Query Editor is based on Power Query syntax instead of DAX. So you should use "Date.Year()" function.

Regards,

[dilumd · Accepted solution]

Hi,

You can do this in the Query editor itself, just take a duplicate of the datetime column you have, then right click and go to below.