Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Cumulative addition of investment yield back (...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Cumulative addition of investment yield back (used Productx function)

02-23-2021
02:17 PM

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.

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-25-2021
07:51 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-23-2021
02:52 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-24-2021
06:57 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-25-2021
07:51 AM

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:

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-25-2021
08:51 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-25-2021
09:18 AM

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

Announcements

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

Featured Topics