Add custom column with end of month (from month number)

[cpdanielmc21]

Hi

I am trying to create a custom column in Power BI query editor to display the last day of the month in every row, however the only reference I have in my table is the Month number (1, 2, 3 ...,12) and the year (currently only 2020).

I was thinking about using"date.endofmonth"  and reference it to a random date generated from the month number and year, but I just can't find a formula to generate a date from month and year. 

Any idea?

Thanks 

[mahoneypat · Accepted solution]

You can add a custom column in the query editor with this expression, using the Year and MonthNumber columns:

=Date.EndOfMonth(#date([Year], [MonthNumber],1))

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[cpdanielmc21]

Fantastic!, Thanks