Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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] + 90
or 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.
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.
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.
@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] + 90
or to create a Calendar Table for more complicated calculations
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
96 | |
75 | |
63 | |
62 |
User | Count |
---|---|
135 | |
105 | |
104 | |
80 | |
65 |