Converting from years and periods to the first or last date of a month in Power BI is an essential skill. We’ll walk through the steps of working with dates to convert them to the beginning or end of a month and explain some of the pros and cons of different methods of working with dates in Power BI.
Dates can be converted to the first or last day of the month in Power BI using the EOMONTH formula, or you can use the Date.EndOfMonth function in Power Query M. Each method will convert a date, but knowing when to use each one will vary based on your specific use case.
Let’s look at when to convert dates to the beginning or end of month along with some examples of when to use each method in Power BI.
Table of Contents
Why You Should Convert Dates to First or Last Day of the Month
When creating reports or dashboards, it’s important to keep the end user experience in mind. Most financial reports will present data based on the final day of a month to indicate that the end of the month has occurred. Leaving dates as the first of the month may be misleading to readers.
Even if you are planning to aggregate values to the full month or period level to indicate that the report covers the full period, an exact date is typically needed in Power BI to join with a date hierarchy. Date hierarchies are used by Power BI to put dates in the correct order and enables users to drill down into different views of the data, such as monthly, quarterly or annually.
By using a convention such as the last day of a month in Power BI it can decrease confusion and enable date hierarchies which ultimately lead to a better end user experience.
How to Convert Periods and Years into Dates in Power BI
Many accounting systems, ERPs, and other business software will publish reports or back-end SQL tables with data that has already been aggregated to the period and year level. This is especially common when working with Trial Balance data in accounting.
The data has the added benefit of being easier to download and work with because it is already aggregated instead of having to download individual row level detail for daily activities.
The following table highlights a common setup for data coming into Power BI. It presents period, representing the number of the month along with a year column.
The challenge with this type of data is that there is no way to join it against other tables based on date, or easily add a relationship to a standard date table without adding period and year columns to the specific date hierarchy and then building a relationship across both columns.
Many DAX Functions and analysis techniques in Power BI will require a date hierarchy for time intelligence. It’s also an essential part of building a dashboard.
Convert Years and Periods to Dates using Power Query
To convert years and period numbers to a date in Power BI, follow these steps. Convert the Period and Year to text. Next, add a custom column to concatenate the period and year with the 1st day of the month. Click OK and then convert the new column to a date data type. A Date.EndOfMonth formula can be added as a second step to convert from the first to last day of the month.
We’ll explain the steps further below.
Step 1.) Launch the Power Query Editor
Step 2.) Convert the Period and Year Columns to Text. This step is required because we are going to concatenate the year and period into a date. You can only concatenate text data types in Power BI.
To convert a column to a text data type, right click on it, then select Change Type, Text.
You can hold down SHIFT to select multiple columns at one time.
Step 3.) Add a Custom Column to concatenate the year and period with the first day of the month.
The fastest way to add a custom column in Power BI is to left click on the actions button at the top left of Power Query.
You can also Add a Custom Column from the Power Query Ribbon.
Use the following formula to concatenate the period and year into a date.
[Period]&"/1/"&[Year]The things to know about the formula are:
- Column Names are surrounded by left and right [ ] signs
- Power BI uses the & symbol to concatenate text
- The middle section of “/1/” uses quotes to define /1/ as text.
Once completed the setup for the Custom Column options will look like the following.
Press OK and a New Column will be created that combines the period and year to create a date column.
Step 4.) Right click on the Date column header and change the data type to Date.
The results will be a new date column that has the first day of the month listed.
Let’s look at how to convert a date to the last day of the month using Power Query in Power BI
How to Convert Dates to Last Day of the Month in Power BI
To convert a date to the last day of the month in Power BI, users can use Power Query or DAX formulas. To make the conversion in Power Query, launch the Power Query Editor, add a New Custom Column and use the Date.EndOfMonth([Date]) formula pointed at the date column you wish to convert. The results will be a new column that shows the last day of the month.
Let’s look at both methods, starting with Power Query.
Step 1.) Add a new custom column
Step 2.) Apply the Power Query Formula Date.EndOfMonth([Date[) where [Date] is the name of the field being convert from a date to the last day of the month.
Step 3.) Press OK
The result is a new column we created called EOM to represent End of Month. It converts the Date Column to the last day of the same month.
How to Convert Dates to First of the Month in Power BI
To convert a date to the First of the Month using Power Query in Power BI, Add a New Custom Column, and apply the formula Date.StartOfMonth([Date]) where Date is the name of the column being converted. Power Query will automatically calculate the first day of the month.
A low code alternative in Power BI is to create a Custom Column from Example. Power BI uses AI to detect patterns in a column that you provide examples for.
Within Power Query, click Add Custom Column from example. The following interface appears where a blank column is created on the right side of the screen. Begin typing in examples of the desired output.
Power BI will suggest a Power Query M formula at the top left and a preview of common requests and results.
When you select one and click OK the remaining column will be filled in with the formula. It’s a fast way to generate formulas that are commonly used without having to remember the syntax.
How to Change Existing Columns in Power Query Instead of Creating a New One
The easiest way to update a column in place is actually to create a new column, delete the old column, and name the new one the same as the old one. This fast three-step process becomes transparent to the rest of the report once Power Query refreshes. By swapping out the name of a column the rest of your Power BI report won’t know the difference.
Converting a date to the Last or First day of the month using DAX EOMONTH Formulas
To use the EOMONTH formula in Power BI, Create a New Column. (Do not create a New Measure as the data should not be aggregated) then apply the formula End of Month = EOMONTH([Date],0) where [Date] is the name of the column and 0 is the offset in months.
To expand on this further, we’ll provide several examples, using a starting column named Date.
The following formula converts a Date to the Last Day of the Month using DAX
This EOMONTH DAX Formula provides the last day of the prior month because of the -1 offset in the formula
Use the EOMONTH DAX Formula to Calculate the First of the Current Month by using -1 as the offset month and adding 1 day to it.
While our general preference is to use Power Query over DAX to create columns, the EOMONTH calculation is a quick one to be able to adjust dates for smaller datasets.
Note: For the DAX Conversion to work, the column must be assigned as a Date or DateTime Format as a starting point.
When to Use DAX vs Power Query for Date Conversions in Power BI
The biggest difference between DAX and Power Query is the time at which the transformation or calculation executes. Power Query will execute a transformation to adjust or create a new column whenever the data is refreshed. DAX will perform calculations when users request a different view or filter of the data. Because DAX calculates upon user request, it can slow down a dashboard.
However, there are times, such as working with a live connection in Power BI where you do not have the ability to modify the data model like you would in import mode. In these cases and several others, you would have to use DAX formulas for the conversion.
We put together a guide that explains when to use DAX vs Power Query if you would like to learn more.
Conclusion
Our preferred method to convert dates to the start of the month or end of the month is using Power Query. Power Query Formulas such as Date.EndOfMonth can quickly perform transformations to an entire column. Updating a column in place can be performed in a multi-step process that results in a new column being named the same as the old one.
If you are working with a smaller dataset, you can use the DAX formula EOMONTH to create a New Column to perform the calculation. The DAX formula consists of two parts, defining the target column, and if you want to offset the number of months. Use 0 to keep the new end of month calculation in the same period as the target column