How to Get the First and Last Date of a Month using only the Month and Year in Power BI
Often there are times were we pull data and group by month and year because bringing in the entire data set by transaction date would be to large. However, when linking to other data sets in Power BI we sometimes will need to create a calculated field with first date and last date of the month and year.
This will be s short and sweet post. However, as you will see it’s really quite simple but very useful.
You first first start out with a table with two columns. One named “TheMonth” and another named “TheYear” with some records with the respected data.
Under the Modeling tab create the following two columns:
- New Column: FirstDayOfMonth = EOMONTH(Date(Materials[TheYear],Materials[TheMonth],10),-1)+1
- New Column: LastDayOfMonth = EOMONTH(Materials[FirstDayOfMonth],0)
All you need to do above is change the table name from “Materials” to the name of your table.
Please leave your feedback. I hope this article has been helpful for you and bookmark this blog and check back often as new articles will be posted regular.