Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

DATEADD not aggregating

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 TypeTotal Order Value3 month6 month9 month
Net New119,025,04532%44%89%
Upgrade6,749,00042%68%74%
Add-on61,386,74150%59%92%
Other3,253,57960%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 NumberTotal Order Value3 Month6 Month9 Month
30033566$274,85928.59%46.42%74.95%
 
TIA
Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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.