Table of Contents
In Power BI, months can be added to a date by using the DATEADD function. This function allows you to add a specified number of months to a given date. For example, if you have a date column in your dataset and you want to add 3 months to each date, you can use the following formula: DATEADD(‘Table'[Date],3,MONTH). This will add 3 months to each date in the ‘Date’ column of the ‘Table’ table. The result will be a new column with the updated dates. This is useful for creating time-based analysis and visualizations in Power BI.
You can use the EDATE function in DAX to add a certain number of months to a date in Power BI.
This function uses the following syntax:
EDATE(start_date, months)
where:
- start_date: The starting date (in a valid date format)
- months: An integer that represents the number of months to add
For example, you could use the following formula to create a new column named Add Four Months that adds four months to the existing Date column in a table named my_data:
Add Four Months = EDATE('my_data'[Date], 4)
The following example shows how to use this formula in practice.
Example: How to Add Months to Date in Power BI
Suppose we have the following table named my_data in Power BI that contains information about sales made on various dates by some company:
Suppose that we would like to create a new column that adds four months to each date in the Date column.
To do so, click the Table tools tab, then click the icon called New column:
Then type the following formula into the formula bar:
Add Four Months = EDATE('my_data'[Date], 4)
This will create a new column named Add Four Months that adds four months to each date in the existing Date column:
Note that you could also subtract a specific number of months by using the following syntax:
Subtract Four Months = EDATE('my_data'[Date], -4)
This will create a new column named Subtract Four Months that subtracts four months from each date in the existing Date column:
Feel free to replace the 4 in the formula to add or subtract however many months you would like from an existing date column.
Note: You can find the complete documentation for the EDATE function in DAX .
Additional Resources
The following tutorials explain how to perform other common tasks in Power BI: