how to correct error: Function 'DATEADD' expects a contiguous selection when the date column comes..

[sujitjena]

Hi,

I have a perfectly working model with a separate "Dates" table that is connected to fact table "CCR". The data was flowing well untill the source data (access file) was updated for current period (Jan & Feb) and when i loaded the data, all works fine except an error "Function 'DATEADD' expects a contiguous selection when the date column comes from a table on the 1-side of a bi-directional relationship". I checked the fact table and the date column is continuous without any blanks etc. So, i am confused what to look for in the data feed that is causing this error. I know my model works.

My calc = CY = sum(CCR[sales])

                 LY = calculate([Sales],dateadd(Dates(Date),-1,year)

Slicer selection = Year = 2021 (only one year is selected and multiple months are selected). It works when i select Jan and Feb but doesnt work when i select other months. My financial calendar follows Sep to Aug.

Any suggestions will be very helpful.

[daxer-almighty]

@sujitjena 

As the error says you can't have periods selected that are not contiguous to one another. So, if you select Jan and Feb in the same year it'll work, but if you select Jan and March, say, in the same year, it'll not work. Easy as that. If you want to be able to work with non-contiguous periods of time, you'll have to use GENERATE and go day by day shifting the days using DATEADD.

[sujitjena · Accepted solution]

@daxer-almighty : I have the periods selected in order i.e Jan and Feb. Anyway, i found the solution. It was with the relationship that was causing the issue. But thanks for your reply.

[omarelmb123]

Can u please share what s wrong with the relation

[LakshX]

Not OP, but I had a similar issue and it got solved by : Editing the relationship between the date and other table -> Change cross filter direction to "single".

[HotChilli]

I've not seen that error before but most DATEADD problems arise from a badly formed date table.  What does the date table look like?

Can you also explain why the date table has a bi-directional relationship? This is unusual.

And this bit is confusing "Slicer selection = Year = 2021 (only one year is selected and multiple months are selected)."

[sujitjena]

@HotChilli : Thanks for your reply. My Date table is derived from the below formula:

Dates = VAR BaseCalendar =CALENDARAUTO(8) RETURN GENERATE(BaseCalendar, VAR BaseDate = [Date] VAR YearDate=YEAR(BaseDate) VAR MonthNumber = MONTH(BaseDate) RETURN ROW("Day",BaseDate, "Year", YearDate, "Month Number",MonthNumber, "Month", FORMAT(BaseDate,"MMMM"),"Year Month", FORMAT(BaseDate,"MMM YY")))

Fin Year = if(MONTH(Dates[Date])<=8, YEAR(Dates[Date]) ,YEAR(Dates[Date])+1)

Actually I dont have any bi-directional relationship.

When i say 1 year - i mean i have two slicers - One for the year and one for the months, I have selected Year=2021 and under Months = Sep to Feb are selected.

I got a feeling that Pbix file might be corrupted, because the slicer is stuck with old table and doesnt take anything that is applied for change.