Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Endofmonth based on month/year column

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 ...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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)
Cookistador's avatar
Cookistador
Icon for Super User rankSuper User
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    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.