Power Pivot in Excel - add Calculated Column to table that uses other rows in the Table - error

[MarkMahon]

We created a table using Power Query in Excel.

We added the table to the Data Model.

We want to add a column that averages the subscriptions in the current month (=current record) with those in the prior two months; the number of months to average can vary with program.

We created a measure which returns an error message.

What is the proper dax syntax or approach? Thank you for your attention.

Mark

Table:

Attempted Measure / Calculated Column:

    Calculated Col
    =
    VAR curNumMoYr = 'Table'[NumMoYr]
    VAR curPlan = 'Table'[plan_name]
    VAR curProgram = 'Table'[program]
    VAR curMoInAvg = 'Table'[MoInAvg]
    
    VAR curSUM = 
    CALCULATE (
    SUM ( 'Table'[written subs] ),
    FILTER (
    'Table',
    'Table'[plan_name] = curPlan &&
    'Table'[program] = curProgram &&
    'Table'[NumcMoYr] >=  curNumMoYr - curMoInAvg + 1 &&
    'Table'[NumcMoYr] <=  curNumMoYr
    )
    )
    
    RETURN
    DIVIDE(curSum, curMoInAvg, 0)

Error Message:

"The Query did not run of the Data Model could not be accessed. Here's the error message we got:
The query referenced column 'Table'[Calculated Col] which depends on another column, relationship of measure that is not in a valid state"

[MarkMahon]

Hi _kalyj - first of all, thank you for the reply.  

Second, with your help I got the formula to work for my application ... so thank you a second time.

The difference between your example (no error) and my work (error) is where the formula was placed ...

• you set it up directly in the calculated column.
• i set it up as a "measure" and then referenced it in the calculated column.

Best Regards,

Mark

[v-yanjiang-msft · Accepted solution]

Hi @MarkMahon ,

I create a sample and after I click Add to Data Model in Excel>Power Pivot, and add a column by your formula, get the correct result.

Haven't found the error you provided. I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.