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.

Picture showing the Power BI data sample.

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.

Share the knowledge!

Paul Rodrigues

Business Analyst with 20 years of IT experience creating practical solutions. I love to automate business processes through the use of technology while making the end users work easier. My current favorite tools are Power Apps, Power BI and Power Automate. #PowerAddict