Copy column and set date to End of Month

[Anonymous]

I'm trying to create a new column that is the calculated end of month from the date in another column.  Using this formula, but getting error that "EOMONTH" wasn't recognized

= Table.AddColumn(#"Renamed Columns", "AccountingDate", each EOMONTH([DATE],0))

[Anonymous · Accepted solution]

Hi @Anonymous, it looks like you are trying to do this in the Query Editor as most of the code below is Power Query functions. DAX can't be used in the Query Editor, so you can't use the EOMONTH() DAX function.

Is there a reason you are doing this inside the query editor, rather than doing it in the "Modeling" tab of Power BI desktop (after you load the data from your query into your model)?

If you must do this within the Query Editor, try using the M function Date.EndOfMonth. You can find more details on the syntax here: https://msdn.microsoft.com/en-us/library/mt260695.aspx

[v-ljerr-msft · Accepted solution]

Hi @Anonymous,

As JaredK mentioned above, to use EOMONTH Function (DAX), you need to go to Modeling tab -> click "New Column" and enter your DAX formula.

NewColumn = EOMONTH(Table1[DateTimeStamp], 0)

For more details about how to create calculated columns in Power BI Desktop, you can refer to this article.

Regards

[Anonymous · Accepted solution]

Hi @Anonymous, it looks like you are trying to do this in the Query Editor as most of the code below is Power Query functions. DAX can't be used in the Query Editor, so you can't use the EOMONTH() DAX function.

Is there a reason you are doing this inside the query editor, rather than doing it in the "Modeling" tab of Power BI desktop (after you load the data from your query into your model)?

If you must do this within the Query Editor, try using the M function Date.EndOfMonth. You can find more details on the syntax here: https://msdn.microsoft.com/en-us/library/mt260695.aspx