DATEADD DAX function and related issues
DATEADD DAX function is a time intelligence function that returns a column of dates that can be shifted forward or backward in time by the specified intervals.
DATEADD function in power bi is versatile and powerful and can work with year, quarter, month and day levels.
Syntax
DATEADD(<dates>,<number_of_intervals>,<interval>)Syntax Explanation
| Term | Definition |
| dates | A column that contains dates. |
| number_of_intervals | An integer that specifies the number of intervals to add to or subtract from the dates. |
| interval | The interval by which to shift the dates. The value for interval can be one of the following: year, quarter, month, day |
Use cases of the DATEADD DAX function
If you want to compare the sale of the current year with the previous year, then you have to use the following DAX expression.
DateADD = CALCULATE([Total Sales],DATEADD(Dates[Date],-1,YEAR))In the above expression, you can play with the second parameter of the dateadd function.
Minus -1 means you are subtracting one year from the current year, and if you remove the minus sign, then you are adding one year to the current year.
You can go to any time interval forward or backward by changing the second parameter to 1,2,3 etc.
- What is my current sale compared to the last ten days
- What was my previous month’s or last year’s sale compared to the current month’s or current year’s sales?
Flavors of DATEADD DAX Function
Day Over Day
DATEADD(DimDate[Date],-1,DAY) Month Over Month
DATEADD(DimDate[Date],-1,MONTH)Quarter Over Quarter
DATEADD(DimDate[Date],-1,QUATER)Year Over Year
DATEADD(DimDate[Date],-1,YEAR)Essential Rules of DATEADD function
- The first column of the DATEADD function should have the type date and return a single-column data table.
- When you add or subtract any interval from the date, you will get only those dates available in your date table.
- After shifting the date interval forward or backward, the DATEADD function will only contain those shifted dates that are available in the data table; otherwise, it will return blanks, as you can see below.
In the above example, you can see that DATEADD is returning blank values. The reason is that it can only produce the dates available in the dataset.
Looking closely, we have six days from August and six days from September. So it produces shifted months for August but blanks for September because there was no data available for August.
On the other hand, if we still want to display shifted months for the missing data, we have one function named EDATE.
EDATE Function in DAX
EDate = EDATE(Data[Date],1)EDATE is a DAX function that takes the following parameters
| Term | Definition |
| start_date | A date in datetime or text format that represents the start date. |
| months | An integer that represents the number of months before or after start_date. |
The EDATE function only works with month intervals, and this interval can be backward or forward.
But if you want to get shifted day, month, quarter, and year for the missing data, then we have some more functions in the power query.
Below you can see step by step explanation.
- Go to Power query editor by clicking on Transform data
- Click on Add column
- Click on the custom column
Here you can write power query functions to achieve your desired results.
These are the functions that we will use in our demo
- Date.AddDays
- Date.AddMonths
- Date.AddQuarters
- Date.AddYears
All these functions take two parameters
- A column that contains dates
- The second parameter is an integer
Syntax of Date.Add functions
Date.AddDays
Date.AddDays([Date],1)Date.AddMonths
Date.AddMonths([Date],1)Date.AddQuarters
Date.AddQuarters([Date],1)Date.AddYears
Date.AddYears([Date],1)
For more useful blogs, please visit Learn DAX
