'DATEADD' Expects a contiguous selection

[Anonymous]

Hello,

I'm trying to create a measure where I calculate revenue recognized on a sales order in specific timeframes (see below table).  I'm using two measures calculating revenue then % of revenue against total sold.

My issue is this measure works when I have the data filtered to one sales order but when I try to apply to my project type (groups) I get the contiguous selection error. It specifically references the first measure that sums the revenue by period, "Calculation error in measure 'Measure'[3 months]: Function 'DATEADD' expects a contiguous selection...".

It seems to me that the measure is not aggregating and I can't figure out how to write it to do so.  I am referencing two different tables 'Bookings' and 'Revenue' in my variable and my return.  Both of these tables have their own date table which may be the issue.

Goal

Percent Revenue Converted by Period
Project Type	Total Order Value	3 month	6 month	9 month
Net New	119,025,045	32%	44%	89%
Upgrade	6,749,000	42%	68%	74%
Add-on	61,386,741	50%	59%	92%
Other	3,253,579	60%	79%	94%

Currently DAX is set up as follows:

Revenue:

3 months =

VAR threemonths = DATEADD(Bookings[Order Intake Date],3,MONTH)

RETURN

CALCULATE([Sum Revenue Recognized], Revenue[Posting Date] <= threemonths)

% Converted = CALCULATE(DIVIDE('Measure'[3 months],[Sum Order Value]))

Again the above works when referencing a single sales order example

Order Number	Total Order Value	3 Month	6 Month	9 Month
30033566	$274,859	28.59%	46.42%	74.95%

TIA

[lbendlin]

change this part

VAR threemonths = DATEADD(Bookings[Order Intake Date],3,MONTH)

to run against the Bookings' Dates table instead.

VAR threemonths = DATEADD(Date_Booked[Date],3,MONTH)

[Anonymous]

There was a change but now my error is "a table of multiple values was supplied where a single value was expected". 

[lbendlin]

Check your code. Especially this part

'Measure'[3 months]

looks suspicious.

[lbendlin]

General guidance is that your data model should contain a calendar/dates table that covers your fact date range (plus a year before) and that has contiguous dates. You'll also want to mark the table as a Date table to avoid wasting model memory.

Then run the DATEADD() against the dates table.

If you don't want to do that you can use EDATE() for month math. It doesn't care about contiguity (sp?)

[kevnotec]

EDATE, yes that's the one!

Just want an equivalent of DATEADD in SQL, albeit will only work for months, but that's just what I need and without all the fuss.

[Anonymous]

Thanks @lbendlin , both fact tables do have their own date tables and are marked as such. DAX is the same for both date tables, the only thing I notice is I don't have a prior year but I can add it. I'll try the EDATE() function.   

Date_Recognized =

VAR Days = CALENDAR ( DATE ( 2016, 1, 1 ), TODAY() )

RETURN ADDCOLUMNS (

Days,

"DR Year", YEAR ( [Date] ),

"DR Month Number", MONTH ( [Date] ),

"DR Month", FORMAT ( [Date], "mmmm" ),

"DR Year Month Number", YEAR ( [Date] ) * 12 + MONTH ( [Date] ) - 1,

"DR Month Year", FORMAT ( [Date], "mmm-yyyy" ),

"DR Year Month", FORMAT ( [Date], "yyyy-mm" ),

"DR Week Number", WEEKNUM([Date], 2),

"DR Year Week Number", (FORMAT ( [Date], "yyyy" )) & "-" & WEEKNUM([Date], 2),

"DR Week Start", FORMAT (([Date] - WEEKDAY ( [Date], 2 ) + 1), "DD-MMM-YY"),

"DR Week End", FORMAT (([Date] - WEEKDAY ( [Date], 2 ) + 7), "DD-MMM-YY"),

"DR Week Name", (FORMAT (([Date] - WEEKDAY ( [Date], 2 ) + 1), "DD-MMM-YY")) & " to " & (FORMAT (([Date] - WEEKDAY ( [Date], 2 ) + 7), "DD-MMM-YY")),

"DR Prev Week Number", (((([Date] - WEEKDAY ( [Date], 2 ) + 1)) - TODAY()) /7),

"DR Working Day", IF(WEEKDAY([Date], 2) <6, 1, 0),

"DR Day Short", FORMAT ( [Date], "ddd" ),

"DR Day Long", FORMAT ( [Date], "dddd" ),

"DR prev Month Number", (YEAR ( [Date] ) * 12 + MONTH ( [Date] ) - 1) - (YEAR ( TODAY() ) * 12 + MONTH ( TODAY() ) - 1),

"DR Current Week", FORMAT ((TODAY() - WEEKDAY ( TODAY(), 2 ) + 1), "DD-MMM-YY"),

"DR Current Month Number", MONTH ( TODAY() ),

"DR Current Year", YEAR ( TODAY() )

)