cancel
Showing results for
Did you mean:
Helper II

## DAX measure to add the value cumulatively

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
2 ACCEPTED SOLUTIONS
Super User

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.

Super User

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.

6 REPLIES 6
Super User

Helper II

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

Super User

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.

Helper II

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

Super User

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.

Helper II

@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] ) )
)

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