Cumulative addition of investment yield back (used Productx function)

[Anonymous]

Hi Everyone,

I am going in circles with the PRODUCTX function for Cumulative Margin ( attached files). Productx function has worked for me previously on a column but in this case (cumulative value), it seems to be not working (may be working but i can't explain the result).

So i have Premium and Loss columns which needs to be cumulatively added along with investment yield. I have calculated the cumulative margin first (running total from Premium-Loss) and tried using the PRODUCTX function with that. This seems to be not working as I can't match the values given in the excel file.  Here is the formula i used (not working);

CumuMarginwithRate=

[CumulativeMargin]*

Calculate(PRODUCTX(table1, 1+InvestmentYield[InvestmentYield Value]/12),

FILTER(All('Table1'[Month]),

         'Table1'[Month]<MAX('Table1'[Month])))

Please let me know if you can understand the result or may be i need to use some other function or logic.

Thank you very much for your help.

power bi file 

Excel calcs 

[AlexisOlson]

In Excel, this calculation is done recursively based on the previous month. You cannot define recursive calculations in DAX, so your options are to find a closed-form workaround or give up.

For more detail and links to a couple of workaround examples, see my StackOverflow post here:

https://stackoverflow.com/questions/52766022/recursion-in-dax

[Anonymous]

Thank you @AlexisOlson for the useful links. However, i am still lost and do not achieve the result i need. If you do not mind, can you please go over the dax formula you have given here, powerbi - DAX - formula referencing itself - Stack Overflow ? I can only get top two values as per excel calcs but from third row values are completely different. Thank you very much for your help again.

[AlexisOlson · Accepted solution]

It's a matter of looking at your recursive definition and trying to figure out how to write it non-recursively.

In your case, you have a series of premiums P1, P2, P3, ... and an initial Cumulative Margin, CM = 0 and you define Investment income as CM*Rate/12 and a recursive rule 

CM = Pprev + CMprev + CMprev * Rate/12
   = Pprev + (1 + Rate / 12) * CMPrev
   = Pprev + (1 + r) * CMPrev

where r is short for Rate/12.

If you start writing out the first few months, you should see a pattern developing:

1: P1
1: CM = 0
1: Income = r * CM = 0
2: P2
2: CM = P1 + 0 + 0
2: Income = r * P1
3: P3
3: CM = P2 + (1 + r) * P1
3: Income = r * (P2 + (1 + r) * P1)
4: P4
4: CM = P3 + (1 + r) * ( P2 + (1 + r) * P1 )
4:Income = r * (P3 + (1 + r) * ( P2 + (1 + r) * P1 ))

5: CM = P4 + (1 + r) * (P3 + (1 + r) * ( P2 + (1 + r)   * P1 ))
      = P4 + (1 + r) *  P3 + (1 + r)^2 * P2 + (1 + r)^3 * P1

In particular, for month N

CM = SUM_{i=1}^{N-1} P_i * (1 + r)^(N-i-1)

Translating this formula into DAX, we can write:

CumulativeMargin = 
VAR r = [InvestmentYield Value] / 12
VAR PrevMonths = FILTER ( ALL ( Table1 ), Table1[Month] < SELECTEDVALUE ( Table1[Month] ) )
VAR AddIndex = ADDCOLUMNS ( PrevMonths, "@Index", RANKX ( PrevMonths, [Month],, ASC ) )
VAR N = COUNTROWS ( PrevMonths )
RETURN
    SUMX ( AddIndex, [Premium] * POWER ( 1 + r, N - [@Index] ) )

Result:

[Anonymous]

Hi @AlexisOlson,

Thank you very much for your wonderful explanation and the DAX to address the recursive calculation. Your logic/dax worked beautifully in this case. I am very thankful to you for going over the logic and dax. I was going in circles for weeks on this calculation. 

I encountered a small hiccup when i tried implementing this on the actual data. So this DAX formula i am trying to use it on a large table (direct query mode), I see the following error. 

Error Message:

The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows.

Just curious if you know or had enountered this issue and could resolve it.  Thanks a lot again!!

[AlexisOlson]

I'd suggest bringing that up as a separate post. I haven't run into that error myself.