cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Vidya09
Helper II
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
lbendlin
Super User
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.

View solution in original post

lbendlin
Super User
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.

View solution in original post

6 REPLIES 6
lbendlin
Super User
Super User

links are requiring dropbox login

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

lbendlin
Super User
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.

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 )
)
 
lbendlin
Super User
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.

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

 

 

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

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.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

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

June 20 episode 7 with aka link 768x460.jpg

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