cancel
Showing results for
Did you mean:
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
Super User

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:

5 REPLIES 5
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

Helper II

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.

Super User

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:

Helper II

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

Super User

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

Announcements

#### 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.

#### Check it Out!

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

#### 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
Top Kudoed Authors