Date function

[pedroccamara]

Hi guys

I'm trying to add a column with a date. I have two columns, two fields, year and month, both numeric values.

The idea is something like this... =DATE((YEAR)Anod, (Month)Mesd) and of course it's not working.

I have two questions:

1. How can i learn the formulas wich are available and how to aply them (youtube videos for example)

2. How do i make this function to work

Many thanks for your reply

Best regards

Pedro

[Anonymous · Accepted solution]

@pedroccamara Are you attempting to do this in the query editor? If so, don't. The above solution is DAX, and you can implement by just right clicking on the table you want to apply the new column to and click on "New colomn" - this is where you would input the DAX syntax.

Using the query editor allows you to use "M" code to create columns etc. 2 different languages.

[v-haibl-msft · Accepted solution]

@pedroccamara

If you want to add the date column in Query Editor, you can add a custom column with following formula.

Number.ToText ( [Month] ) & "/" & "1" & "/" & Number.ToText ( [Year] )

Then change the type of this column to “Date”.

If you want to use DAX, please following the solution provided by Eno1978.

Best Regards,

Herbert

[Anonymous]

@pedroccamara Looks like something like this would work.

Date = FORMAT(([Month] & "/01/" & [Year]), "mm-dd'yyyy")

Then just change the Data Type to Date, and set to whatever format you want. My assumption is that since you don't have a "Day" that you would just default to the first day of the month.

[pedroccamara]

Hello Eno

I've just change the field names like this ... FORMAT(([Mês] & "/01/" & [Ano]), "mm-dd'yyyy") and it gives me an expression error "The name FORMAT wasn't recognized"...

Any ideas?

[Anonymous · Accepted solution]

@pedroccamara Are you attempting to do this in the query editor? If so, don't. The above solution is DAX, and you can implement by just right clicking on the table you want to apply the new column to and click on "New colomn" - this is where you would input the DAX syntax.

Using the query editor allows you to use "M" code to create columns etc. 2 different languages.

[pedroccamara]

You're the MAN!!

Thank you very much

Best regards

Pedro