DAX measure to add the value cumulatively

[Anonymous]

Hi All,

I have a request to calculate the investment income along with updated collateral (investment income + collateral) as shown in the attached excel file. 

As per the calculation shown in the excel file, I will be calculating the 2% of Collateral as Investment Income and adding that income back to the collateral. And the addition of Investment income back to the collateral goes on cumulatively ( i will have to add all the investment incomes from each month previous to current month). Can someone please help me with the DAX measure for this request. Here is my DAX (which doesn't work).

zRRCollateral is a measure too.

CollateralInvestmentIncome =

var PrevInvst = CALCULATE([zRRCollateral],PREVIOUSMONTH(DimTime[Month]))*('zInvestment Yield (%)'[Investment Yield (%) Value]/100)/12

var Prevcollateral = CALCULATE([zRRCollateral],PREVIOUSMONTH(DimTime[Month]))

var CurrentInvst = [zRRCollateral]*('zInvestment Yield (%)'[Investment Yield (%) Value]/100)/12

Var final = CALCULATE(

(Prevcollateral + PrevInvst)*('zInvestment Yield (%)'[Investment Yield (%) Value]/100)/12,

    FILTER(All(DimTime[Month]),

        'DimTime'[Month]<=MAX('DimTime'[Month])))

return final

https://www.dropbox.com/home?preview=Data+for+PowerBi+Community.xlsx 

[lbendlin · Accepted solution]

The moment you have recursive calculations like yours you need to use the PRODUCTX() iterator. CALCULATE won't cut it any more. Be prepared for some really bad performance for tables with many rows.

[lbendlin]

links are requiring dropbox login

[Anonymous]

Sorry about that, I just updated the links to those files. Can you please try it again?

[lbendlin · Accepted solution]

You don't need to use CALCULATE together with PRODUCTX. Instead of 

CALCULATE (
            PRODUCTX ( Data, 1 + InvestmentYield[InvestmentYield Value] / 12 ),
            FILTER ( ALL ( Data[Month] ), 'Data'[Month] < MAX ( 'Data'[Month] ) )
        )

you can write

PRODUCTX (FILTER ( ALL ( Data[Month] ), 'Data'[Month] < MAX ( 'Data'[Month] ), 1 + InvestmentYield[InvestmentYield Value] / 12 )

As for the training courses - frankly you don't need much more than a DIAD, the official documentation, and to read a forum like this one. That's where I got most of my currrent knowledge from.  With the fast pace of Power BI development it helps to re-read the official documentation frequently.

[Anonymous]

hi @lbendlin :

If you don't mind looking into the issue described below would be very helpful.

I have similar calculation to be done on the different set of columns ( Premium and Loss) 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);

CumulativeMarginWithInterest =

([PremMinusLoss running total in Month]*

PRODUCTX (FILTER ( ALL ( rrpowerbi[Month] ), 'rrpowerbi'[Month] <MAX ( 'rrpowerbi'[Month] )), 1 + InvestmentYield[InvestmentYield Value] / 12 )

)

 Excel File 

PowerBI file 

[lbendlin · Accepted solution]

The moment you have recursive calculations like yours you need to use the PRODUCTX() iterator. CALCULATE won't cut it any more. Be prepared for some really bad performance for tables with many rows.

[Anonymous]

@lbendlin : Thank you very much for your quick response. PRODUCTX() worked great in this case. I had no idea about this function, thank you for coming to my rescue. Alternatively, can you please direct me to some useful DAX training/courses? This will be super helpful for me to build a strong foundation.

For other users, following query worked in my case (super fast as well);

FutureCollateralValue =
VAR PrevCollateral =
    CALCULATE ( SUM ( Data[Collateral] ), PREVIOUSMONTH ( Data[Month] ) )
RETURN
    PrevCollateral
        * CALCULATE (
            PRODUCTX ( Data, 1 + InvestmentYield[InvestmentYield Value] / 12 ),
            FILTER ( ALL ( Data[Month] ), 'Data'[Month] < MAX ( 'Data'[Month] ) )
        )