Syntax of the EDATE Function
The EDATE function takes two arguments: the start date, and the number of months to add or subtract. The syntax is as follows:
The start date can be any valid date value, such as a date column in your data model, or a static date entered directly into the formula. The months argument can be any positive or negative integer, indicating the number of months to add or subtract from the start date.
Examples of the EDATE Function in Action
Let's take a look at some practical examples of how the EDATE function can be used in Power BI.
Suppose we have a sales table with a date column and a revenue column, and we want to create a rolling average of revenue over the past three months. We can use the EDATE function to calculate the start and end dates for each rolling window, and then use those dates to filter the sales table and calculate the average revenue.
Rolling Average =
VAR StartDate = EDATE(MAX(Sales[Date]), -2)
VAR EndDate = MAX(Sales[Date])
FILTER(Sales, Sales[Date] ❱= StartDate && Sales[Date] ❰= EndDate),
In this example, we use the MAX function to find the most recent date in the sales table, and then subtract two months using the EDATE function to get the start date for our rolling window. We then use the MAX function again to get the end date, which is always the most recent date in the sales table. Finally, we use the AVERAGEX function to calculate the average revenue over the filtered sales table.
Year Over Year Comparisons
Another common use case for the EDATE function is to perform year over year comparisons. Suppose we have a sales table with a date column and a revenue column, and we want to compare this year's revenue to last year's revenue. We can use the EDATE function to calculate the start and end dates for each year, and then use those dates to filter the sales table and calculate the revenue for each year.
This Year =
YEAR(Sales[Date]) = YEAR(MAX(Sales[Date]))
Last Year =
YEAR(Sales[Date]) = YEAR(EDATE(MAX(Sales[Date]), -12))
Year Over Year Change =
[This Year] – [Last Year],
In this example, we use the MAX function to find the most recent date in the sales table, and then subtract 12 months using the EDATE function to get the start date for last year. We then use the YEAR function to filter the sales table for this year and last year, and use the CALCULATE function to sum the revenue for each year. Finally, we use the DIVIDE function to calculate the year over year change as a percentage.
The Power BI DAX function EDATE is an extremely powerful tool for working with dates in your data analysis. By allowing you to add or subtract a specified number of months from a given date, the EDATE function makes it easy to perform calculations such as rolling averages, year over year comparisons, and more. With the examples provided in this article, you should now have a solid understanding of how to use the EDATE function in your own Power BI reports and dashboards.