Endofmonth based on month/year column

[Anonymous]

Hi all, 

Can anyone help me tell me what the best way is to add a column with end-of-month based on a yyymm-column. I would like to add a date-column for using datefilters. 

What is the most efficient way to achieve this? 

Thanks in advance!

[Vijay_A_Verma · Accepted solution]

1. Use below formula in Power Query, replace Date with your column name

= Date.EndOfMonth(Date.FromText(Text.From([Date])&"01"))

2. If you are planning to use DAX

= EOMONTH(DATE(LEFT([Date],4),RIGHT([Date],2),1),0)

[Anonymous]

Thanks! Exactly what I need. 

[Cookistador]

Hello,
You can do it via Dax or in Power query, if you want to do it in power query, it is possible to do via M with something like

#date(Number.FromText(Text.Start(Text.From([Column1], "en-GB"), 4)),Number.FromText(Text.End(Text.From([Column1], "en-GB"), 2)),31)

#date required 3 parameters year, month and day, 
You can extract Year and month via your yyyymm column, you convert this text to number and it should work

[Anonymous]

Thanks, I'll try this, however end-of-month is not always 31. I will use your formula in a Date.EndOfMonth-function and change the day value to 1.