Forum Discussion
DATEADD function does not work well with filter.
Hi all,
I have a requirement calculate the data last some days period. I explored and used function DATEADD.
I also have some filter in my data, after applied, I assumed it worked well either with filter or not.
But when I have test with filter, the measure with DATEADD seemed like didnot change.
Does anyone can explain or give me a solution of this?
Thank you.
Hi Anonymous
dateadd() is a very tricky function
according https://docs.microsoft.com/en-us/dax/dateadd-function-dax
The result table includes only dates that exist in the dates column. If the dates in the current context do not form a contiguous interval, the function returns an error.so, if your data model doesnt ensure continious of dates it's better to use statements like
Table[Date] + 90or to create a Calendar Table for more complicated calculations
Anonymous , as explained by az38 you can not use dateadd. He also suggested solution. You can also create a date add like this
New Date = date(year(table[date])+0,month(table[date])+0,day(table[date])+0)
+0 is what you have replace in year , month or date
To add 7 days
New Date = date(year(table[date]),month(table[date]),day(table[date])+7)
To add 7 Months
New Date = date(year(table[date]),month(table[date])+7,day(table[date]))
You can add more than 12 month too
Hi Anonymous ,
I add a few blogs and videos that you could reference to learn more about the function of DATEADD.
Using DATEADD() and other Date Tips for Filtering Data in Power BI
The DATEADD Function: The Best And Most Versatile Time Intelligence Function in Power BI
DATEADD function (DAX) - DAX DATEADD function In Power BI Desktop
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Anonymous , as explained by az38 you can not use dateadd. He also suggested solution. You can also create a date add like this
New Date = date(year(table[date])+0,month(table[date])+0,day(table[date])+0)
+0 is what you have replace in year , month or date
To add 7 days
New Date = date(year(table[date]),month(table[date]),day(table[date])+7)
To add 7 Months
New Date = date(year(table[date]),month(table[date])+7,day(table[date]))
You can add more than 12 month too
Hi Anonymous
dateadd() is a very tricky function
according https://docs.microsoft.com/en-us/dax/dateadd-function-dax
The result table includes only dates that exist in the dates column. If the dates in the current context do not form a contiguous interval, the function returns an error.so, if your data model doesnt ensure continious of dates it's better to use statements like
Table[Date] + 90or to create a Calendar Table for more complicated calculations
Hi Anonymous ,
I add a few blogs and videos that you could reference to learn more about the function of DATEADD.
Using DATEADD() and other Date Tips for Filtering Data in Power BI
The DATEADD Function: The Best And Most Versatile Time Intelligence Function in Power BI
DATEADD function (DAX) - DAX DATEADD function In Power BI Desktop
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
Do these answers make sense? If so, please mark the helpful answers as the solutions. Then we will be able to close the case. More people will benefit from here. Thanks in advance.