cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Vidya09
Helper II
Helper II

Cumulative addition of investment yield back (used Productx function)

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.
1 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:

AlexisOlson_0-1614268089240.png

View solution in original post

5 REPLIES 5
AlexisOlson
Super User
Super User

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

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.

 

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:

AlexisOlson_0-1614268089240.png

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!!

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

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors