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
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 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.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors