DATEADD not aggregating

[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

[v-lili6-msft]

hi @Anonymous 

You need  a dim date table and then use it with Time Intelligence function in dax

https://radacad.com/do-you-need-a-date-dimension

If you still have the problem, please share your sample pbix file and your expected output.

Regards,

Lin

[amitchandak]

@Anonymous , Try like

Revenue:
var _book =Bookings[Order Intake Date]
3 months =
VAR threemonths = DATE(year(_book),month(_book) +3,day(_book))
RETURN
CALCULATE([Sum Revenue Recognized], Revenue[Posting Date] <= threemonths)

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

[Anonymous]

Thanks @amitchandak 

So when I try to use 

var _book = Bookings[Order Intake Date] it won't let me reference the column

It says a single value for column 'Order Intake Date' in table 'Bookings' cannot be determined.