topic Re: Cumulative addition of investment yield back (used Productx function) in Desktop
https://community.powerbi.com/t5/Desktop/Cumulative-addition-of-investment-yield-back-used-Productx/m-p/1690048#M672044
<P>It's a matter of looking at your recursive definition and trying to figure out how to write it non-recursively.</P><P> </P><P>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 </P><P> </P><LI-CODE lang="markup">CM = Pprev + CMprev + CMprev * Rate/12
= Pprev + (1 + Rate / 12) * CMPrev
= Pprev + (1 + r) * CMPrev</LI-CODE><P> </P><P>where r is short for Rate/12.</P><P> </P><P>If you start writing out the first few months, you should see a pattern developing:</P><P> </P><LI-CODE lang="markup">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</LI-CODE><P> </P><P>In particular, for month N</P><P> </P><LI-CODE lang="markup">CM = SUM_{i=1}^{N-1} P_i * (1 + r)^(N-i-1)</LI-CODE><P> </P><P>Translating this formula into DAX, we can write:</P><P> </P><LI-CODE lang="markup">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] ) )</LI-CODE><P> </P><P>Result:</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="AlexisOlson_0-1614268089240.png" style="width: 999px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/462913i9EDFC0995F3F343A/image-size/large?v=v2&px=999" role="button" title="AlexisOlson_0-1614268089240.png" alt="AlexisOlson_0-1614268089240.png" /></span></P>Thu, 25 Feb 2021 15:52:45 GMTAlexisOlson2021-02-25T15:52:45ZCumulative addition of investment yield back (used Productx function)
https://community.powerbi.com/t5/Desktop/Cumulative-addition-of-investment-yield-back-used-Productx/m-p/1684767#M670732
<P>Hi Everyone,</P><P>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).</P><P>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);</P><P> </P><P>CumuMarginwithRate=</P><DIV><DIV><SPAN>[CumulativeMargin]*</SPAN></DIV><DIV><SPAN>Calculate(PRODUCTX(table1, 1+InvestmentYield[InvestmentYield Value]/12),</SPAN></DIV><DIV><SPAN>FILTER(All('Table1'[Month]),</SPAN></DIV><DIV><SPAN> 'Table1'[Month]<MAX('Table1'[Month])))</SPAN></DIV><DIV> </DIV><DIV><SPAN>Please let me know if you can understand the result or may be i need to use some other function or logic.</SPAN></DIV><DIV> </DIV><DIV><SPAN>Thank you very much for your help.</SPAN></DIV><DIV><SPAN><A title="Power bi with productx function" href="https://www.dropbox.com/s/1ouwt9sh3lw9qwx/Powerbi%20community%20Power%20Bi%20file.pbix?dl=0" target="_self">power bi file</A> </SPAN></DIV><DIV><SPAN><A title="Excel calcs" href="https://www.dropbox.com/scl/fi/vdtgirl0l8pno7lsgk7vn/Power-BI-Community-Excel-file.xlsx?dl=0&rlkey=7jxjm2nxilq1fqrqwwgxhtnrq" target="_self">Excel calcs</A> </SPAN></DIV></DIV>Tue, 23 Feb 2021 22:17:28 GMThttps://community.powerbi.com/t5/Desktop/Cumulative-addition-of-investment-yield-back-used-Productx/m-p/1684767#M670732Vidya092021-02-23T22:17:28ZRe: Cumulative addition of investment yield back (used Productx function)
https://community.powerbi.com/t5/Desktop/Cumulative-addition-of-investment-yield-back-used-Productx/m-p/1684806#M670742
<P>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.</P><P> </P><P>For more detail and links to a couple of workaround examples, see my StackOverflow post here:</P><P><A href="https://stackoverflow.com/questions/52766022/recursion-in-dax" target="_blank" rel="noopener">https://stackoverflow.com/questions/52766022/recursion-in-dax</A></P>Tue, 23 Feb 2021 22:53:10 GMThttps://community.powerbi.com/t5/Desktop/Cumulative-addition-of-investment-yield-back-used-Productx/m-p/1684806#M670742AlexisOlson2021-02-23T22:53:10ZRe: Cumulative addition of investment yield back (used Productx function)
https://community.powerbi.com/t5/Desktop/Cumulative-addition-of-investment-yield-back-used-Productx/m-p/1688189#M671606
<P>Thank you <LI-USER uid="39298"></LI-USER> 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, <A href="https://stackoverflow.com/questions/60641059/dax-formula-referencing-itself" target="_blank">powerbi - DAX - formula referencing itself - Stack Overflow</A> ? 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.</P><P> </P>Thu, 25 Feb 2021 02:57:17 GMThttps://community.powerbi.com/t5/Desktop/Cumulative-addition-of-investment-yield-back-used-Productx/m-p/1688189#M671606Vidya092021-02-25T02:57:17ZRe: Cumulative addition of investment yield back (used Productx function)
https://community.powerbi.com/t5/Desktop/Cumulative-addition-of-investment-yield-back-used-Productx/m-p/1690048#M672044
<P>It's a matter of looking at your recursive definition and trying to figure out how to write it non-recursively.</P><P> </P><P>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 </P><P> </P><LI-CODE lang="markup">CM = Pprev + CMprev + CMprev * Rate/12
= Pprev + (1 + Rate / 12) * CMPrev
= Pprev + (1 + r) * CMPrev</LI-CODE><P> </P><P>where r is short for Rate/12.</P><P> </P><P>If you start writing out the first few months, you should see a pattern developing:</P><P> </P><LI-CODE lang="markup">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</LI-CODE><P> </P><P>In particular, for month N</P><P> </P><LI-CODE lang="markup">CM = SUM_{i=1}^{N-1} P_i * (1 + r)^(N-i-1)</LI-CODE><P> </P><P>Translating this formula into DAX, we can write:</P><P> </P><LI-CODE lang="markup">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] ) )</LI-CODE><P> </P><P>Result:</P><P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="AlexisOlson_0-1614268089240.png" style="width: 999px;"><img src="https://community.powerbi.com/t5/image/serverpage/image-id/462913i9EDFC0995F3F343A/image-size/large?v=v2&px=999" role="button" title="AlexisOlson_0-1614268089240.png" alt="AlexisOlson_0-1614268089240.png" /></span></P>Thu, 25 Feb 2021 15:52:45 GMThttps://community.powerbi.com/t5/Desktop/Cumulative-addition-of-investment-yield-back-used-Productx/m-p/1690048#M672044AlexisOlson2021-02-25T15:52:45ZRe: Cumulative addition of investment yield back (used Productx function)
https://community.powerbi.com/t5/Desktop/Cumulative-addition-of-investment-yield-back-used-Productx/m-p/1690195#M672089
<P>Hi <LI-USER uid="39298"></LI-USER>,</P><P> </P><P>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. </P><P> </P><P>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. </P><P>Error Message:</P><P>The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows.</P><P> </P><P>Just curious if you know or had enountered this issue and could resolve it. Thanks a lot again!!</P>Thu, 25 Feb 2021 16:51:28 GMThttps://community.powerbi.com/t5/Desktop/Cumulative-addition-of-investment-yield-back-used-Productx/m-p/1690195#M672089Vidya092021-02-25T16:51:28ZRe: Cumulative addition of investment yield back (used Productx function)
https://community.powerbi.com/t5/Desktop/Cumulative-addition-of-investment-yield-back-used-Productx/m-p/1690244#M672103
<P>I'd suggest bringing that up as a separate post. I haven't run into that error myself.</P>Thu, 25 Feb 2021 17:18:58 GMThttps://community.powerbi.com/t5/Desktop/Cumulative-addition-of-investment-yield-back-used-Productx/m-p/1690244#M672103AlexisOlson2021-02-25T17:18:58Z