cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Syndicate_Admin
Administrator
Administrator

Problem with function "DATEADD"

Good fellows, I hope you can help me.

I'm trying to calculate the variation month by month with the following measure:

JuanDtM23_1-1620016421568.png

However, I get the following error:

JuanDtM23_2-1620016470725.png

and as you can see, the data in the column, is date-type:

JuanDtM23_3-1620016540292.png

In advance thank you very much for all the help you can give me

1 ACCEPTED SOLUTION
v-henryk-mstf
Community Support
Community Support

Hi @Syndicate_Admin ,

 

Which connection mode does the data source you connect to adopt? For the DATEADD function, if it is in the Direct query mode, there may be performance problems, leading to errors.

v-henryk-mstf_0-1620183058153.png

 

Here is the reference document link:DATEADD function (DAX) - DAX | Microsoft Docs

 

Please make sure to use this function correctly and change the connection mode before trying again. If the problem is still not resolved, please let me know immediately, looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-henryk-mstf
Community Support
Community Support

Hi @Syndicate_Admin ,

 

Which connection mode does the data source you connect to adopt? For the DATEADD function, if it is in the Direct query mode, there may be performance problems, leading to errors.

v-henryk-mstf_0-1620183058153.png

 

Here is the reference document link:DATEADD function (DAX) - DAX | Microsoft Docs

 

Please make sure to use this function correctly and change the connection mode before trying again. If the problem is still not resolved, please let me know immediately, looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Jihwan_Kim
Community Champion
Community Champion

Hi,

I am not sure how your data model looks like, but in my opinion, you need to have dim-date table.

And then, please try to write something like below.

 

Variations =
VAR current_value =
SUM ( factpagmb[valor facturado] )
VAR previous_value =
CALCULATE (
SUM ( factpagmb[valor facturado] ),
DATEADD ( 'your dim-date table'[date column], -1, MONTH )
)
RETURN
DIVIDE ( current_value - previous_value, current_value )

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

amitchandak
Super User IV
Super User IV

@Syndicate_Admin , Dateadd work on date.

so [Date].mes is not correct.

 

Also, prefer to date table in such case

 

example

last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

 

if needed you can use date as date add

https://www.youtube.com/watch?v=9qiRivlBv8w



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors