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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
CraigBlackman
Helper III
Helper III

Function 'DATEADD' only works with contiguous date selections.

Hi All,

 

I have this error which is a measure calculated from net sales DATEADD -1 year.

 

MdxScript(Model) (1, 134) Calculation error in measure 'DimDate'[P/Y Sales1]: Function 'DATEADD' only works with contiguous date selections.

 

Unsure how i get over this so any help really appreciated.

 

Thanks 

 

Craig

1 ACCEPTED SOLUTION

@CraigBlackmanYou can try  P/Y Sales = CALCULATE ( [Net Sales]; SAMEPERIODLASTYEAR( Dates[Date]))

or wrap your P/Y Sales with SUMX 

P/Y Sales = SUMX ( salestable; calculate( [Net Sales]; DATEADD( Dates[Date]; -1 ; Year )) 

 

Konstantinos Ioannou

View solution in original post

9 REPLIES 9
konstantinos
Memorable Member
Memorable Member

The problem usually is the Grand total or a graph which has more year values...you can wrap the formula IF(HASONEVALUE(Dates[Year]); yourmeasure)

http://www.powerpivotpro.com/2014/01/defanging-the-contiguous-date-selections-error/
Konstantinos Ioannou

Hi,

 

Thanks for that. The issue is that I want to display a total for these columns.

 

I have this table, the P/Y Sales and P/Y Orders a\re the problem columns here, both based on the current date -1 Year.

 

Maybe I can calacultae this in another fashion?

 

Capture.PNG

@CraigBlackmanYou can try  P/Y Sales = CALCULATE ( [Net Sales]; SAMEPERIODLASTYEAR( Dates[Date]))

or wrap your P/Y Sales with SUMX 

P/Y Sales = SUMX ( salestable; calculate( [Net Sales]; DATEADD( Dates[Date]; -1 ; Year )) 

 

Konstantinos Ioannou

I have tried to make the formula to calculate the variation of a measure:

PYTDInversionTotal = SUMX (Fact_Market; CALCULATE ([Total Investment]; DATEADD (Dim_EmissionAmission Notice [TimeKey]; - 1; YEAR)))

But when I take it to my model it does not calculate me any value. What could be the problem?

Thanks Kostas. That helped me too!

Hi @konstantinos, I'm a new guy here in the community and a newbie using DAX and Power BI. I had the same function error but it turns me to this approach by getting to resolved the same issues. Having alike, the documentation can be found here => https://msdn.microsoft.com/en-us/library/ee634763.aspx are definetly could help a lot.

The scenario is that this table has an incomplete DateMonth in one particular year in the middle of a FiscalYear ranges from 2001-2016. By having a slice n' dice using this filter to my Fact table then one of the year has incontiguous records by then the error shows. 

The DAX script below shows to me on how to get rid off the issue to an incontiguous list of dates in my CalendarFiscal table.

[Measure] = TOTALYTD(CALCULATE(SUM(FactTable[Amount]), DATEADD(Calendar[Date], -1, YEAR)), Calendar[Date], ALL(Calendar), "6/30")

Yep, it works to me.

@konstantinos

 

After a bit of trial and error, the SUMX wrap has done the job, so many thanks for your help. Greatly appreciated.

 

Craig Blackman

Hi all, new to the forum and new to DAX as well.  It seems like many people have this same question, yet I haven't yet been able to find an answer that works in my situation.

 

What I want to do.  Compare sales year to date for current year (for example 1/1/2015 to 11/2/2015) to sales year to date previous year (for example 1/1/2014 to 11/2/2014)

 

Here's my two formulas so far   

Sales YTD:=TOTALYTD(sum(Sold[ExtPrice]),Sold[OrderDate])

 

Sales Prev YTD:=CALCULATE(SUM(Sold[ExtPrice]),DATESBETWEEN(Calender[DateKey],date(2014,1,1),LASTDATE(DATEADD(Sold[OrderDate],-365,DAY))))

 

My formula works fine in PowerPivot View, but I can't get around the dreaded contiguous date error whenever I create a pivot table.  I've tried messing with various IF(HASONEVALUE.... and SUMX... but haven't gotten a solution.  Again, I'm pretty new to all this so my understanding of some of these functions is pretty limited.  Any help would be greatly appreciated.

 

kcantor
Community Champion
Community Champion

@sksmith It seems that you are pulling a date for your Sales YTD from the fact table and not from your calendar table. Try:

Sales YTD: = TOTALYTD(SUM(Sold[ExtPrice]), Calender[DateKey]))

Do the same for your other calculations. Pull the date from the date table as time intelligence needs the date table to function correctly.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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