Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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)
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)
Thanks! Exactly what I need.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.