Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

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.

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)))

Hi @amitchandak,

 

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

 

Regards.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.