cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PwrBI01
Post Patron
Post Patron

Calculation error in measure: Function 'DATEADD' expects a contiguous selection when the date column

Hi guys,

 

I am trying to create a measure to calculate the variation between 2 years with the following measure:

Expenses variation = 
Var CurrentExpenses = TOTALYTD(SUM(Sales[Sales]);'Calendar table'[Sales])
Var PreviousExpenses = CALCULATE(TOTALYTD(SUM(Sales[Sales]);'Calendar table'[Sales]);DATEADD('Calendar table'[Sales];-12;MONTH))
Return
(CurrentExpenses-PreviousExpenses)/PreviousExpenses

 

But I got the following error message:

 

Imagen2.png

 

The relationship between both tables is the following one:

Imagen3.png

 

How can I solve that?

 

Thanks in advance.

 

Regards.

1 ACCEPTED SOLUTION

Hi @v-janeyg-msft,

 

Thank you for your answer.

 

I was creating a simple data and I have found were the problem was.

 

I had a not activated relationship between Calendar table [Year] and another table with a both direction filter. When I changed that into single direction filter the problem was solved.

 

Regards.

View solution in original post

6 REPLIES 6
v-janeyg-msft
Community Support
Community Support

Hi, @PwrBI01 

 

Generally speaking, it is caused by discontinuous data or incorrect relationship, Please check it.

If it doesn't help, I think you need to provide a sample file,then we can judge.

 

Best Regards

Janey Guo

 

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

Hi @v-janeyg-msft,

 

What do you mean by discontinuous data?

 

Thanks in advance.

 

Regards.

Hi, @PwrBI01 

 

The date using the time intelligence function must be continuous. Please check it.

 

Best Regards

Janey Guo

 

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

Hi @v-janeyg-msft,

 

Thank you for your answer.

 

I was creating a simple data and I have found were the problem was.

 

I had a not activated relationship between Calendar table [Year] and another table with a both direction filter. When I changed that into single direction filter the problem was solved.

 

Regards.

View solution in original post

amitchandak
Super User
Super User

@PwrBI01 , Mark Calendar table as a date table, That will check for any issue with noncontinuous dates. Right-click on the table there is an option 

Also, Try like

CALCULATE(TOTALYTD(SUM(Sales[Sales]);DATEADD('Calendar table'[Sales];-12;MONTH)))



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!

Hi @amitchandak,

 

Thank you for your answer, I have tried both ways but it doesn't work.

 

Regards.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!