Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Calculating IRR using slicer and projected data

Hello

 

I am trying to calculate the projected IRR for a particular company, i.e. the IRR on a particular date in the future. I have two relevent data tables that I am using, one that provides the various cashflows and one that contains the various fields used to calculate the valuation. 

 

DateValueTypeCompany
1/1/20221000RealisationCompany A
2/1/2022-2000InvestmentCompany A
3/1/20223000FeesCompany A
4/1/202210000Enterprise Value (EV)Company A
5/1/20224000InvestmentCompany B

 

DateCompanyEBITDAMultipleEnterprise Value (EV)
4/1/2022Company A5000210000

 

I am using a series of slicers to create different adjustments to the EV calculation, i.e. increase multiple by 5%. This then creates a projection of what the EV will be in the future, based on the various adjustments to the variables:

 

 
Projected_EV=
SUMX(Company_A_valuation, Company_A_valuation[EBITDA] * (1 + [%_EBITDA_slicer]) * (Company_A_valaution [multiple] + [%_multiple_slicer]))

 

I am trying to work out how i can use the projected EV and a chosen date in future to feed into the cashflow table and therefore be included in the IRR calculation. I have tried various custom columns to duplicate the cashflow, but also bring in the projected EV at the selected date, but I cannot get it to work. 

 

Any thoughts or suggestions welcome! Thank you in advance

 

NB to select the date of when the EV is to be taken from, I have been playing around with a DAX similar to this:

 

'Cashflow'[Projected_IRR] = 
VAR currentdate = MAX('Calendar'[Date])
RETURN IFERROR(IF(CALCULATE(SUM(CashFlow[end amt]),FILTER('Calendar','Calendar'[Date]=currentdate)) = BLANK(),BLANK(), XIRR( FILTER( SUMMARIZE( FILTER( ALL('Calendar'), Calendar[Date]<=currentdate ), Calendar[Date], "TotalCashFlowIRR", IF('Calendar'[Date]=currentdate,SUM(CashFlow[end amt]),BLANK()) + CALCULATE(SUM(CashFlow[flow])) ), [TotalCashflowIRR] <>0 ),

 

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

You could add in the future date and value by creating a variable as the UNION of the base data and ROW for the future projections, something along the lines of

Rate of return =
VAR tmpTable =
    UNION (
        SELECTCOLUMNS (
            FILTER ( 'Base Table', 'Base Table'[Some column] = "filter value" ),
            "_Date", 'Base Table'[Date],
            "_Value", 'Base Table'[Value]
        ),
        ROW (
            "_Date", SELECTEDVALUE ( 'Future Dates'[Date] ),
            "_Value", [Projected future value measure]
        )
    )
RETURN
    XIRR ( tmpTable, [_Value], [_Date] )

View solution in original post

3 REPLIES 3
johnt75
Super User
Super User

You could add in the future date and value by creating a variable as the UNION of the base data and ROW for the future projections, something along the lines of

Rate of return =
VAR tmpTable =
    UNION (
        SELECTCOLUMNS (
            FILTER ( 'Base Table', 'Base Table'[Some column] = "filter value" ),
            "_Date", 'Base Table'[Date],
            "_Value", 'Base Table'[Value]
        ),
        ROW (
            "_Date", SELECTEDVALUE ( 'Future Dates'[Date] ),
            "_Value", [Projected future value measure]
        )
    )
RETURN
    XIRR ( tmpTable, [_Value], [_Date] )
Anonymous
Not applicable

Thanks for the suggestion. The principle of this works well and the table created works with the XIRR function, so I am half way there. 

 

However, for the additional ROW I am struggling to work out a way for the user to be able to select a particular date in the future (currently trying to use a slicer) and then associate a value with that date, in order to use this projected valuation as part of IRR calculation. 

 

I have tried, for example, to use this measure:

Selected_year = 
        CALCULATE(
        MAX( Calendar[Date] ),
        ALLSELECTED( Calendar[Date] )

 

to link a slicer to the DAX you suggest, but the _date in the new ROW is unresponsive and just uses the last date. 

 

Are you able to suggest a measure to use for _date and _value for the new ROW that would allow the user to select a time period and the respective value would be used?

 

Create a separate date table which is not connected to anything, just to use for the slicer. That's what I intended the 'Future Dates' table to be in my initial code.

For the value to use, you could use a what if parameter. That automatically creates a measure which returns the selected value, so you can just use that

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.