Forum Discussion

dmbd1904's avatar
dmbd1904
Helper III
6 years ago
Solved

XIRR DAX Help

Hi I've been trying for hours to make this work without success. Does anybody know if it's possible to use a measure as the cashflow part of a XIRR function?   Measure 2 = VAR CALC2= (-CALCULAT...
Need Help
  • dmbd1904's avatar
    dmbd1904
    6 years ago

    Hi v-xicai, the issue was where the cashflow calculations returned a blank value. To overcome this I used an IfError statement within the XIRR calc. Thanks for your replies

v-xicai's avatar
v-xicai
Community Support
6 years ago

Hi dmbd1904 ,

 

For the article of XIRR function, we know the table in function is like cashflow table instead of a calendar table,  You may change the formula like DAX below. Or you may show us your formula error message for further analysis.

 

Syntax: XIRR(<table>, <values>, <dates>, [guess])

 

Measure 2 =

VAR CALC2=

(-CALCULATE(

    SUM('Asta'[Property Commercial Value]),Asta[Activity]="Ready to Snag",Asta[Attribute]="Forecast Finish"))

+

CALCULATE(

    SUM('Salesforce Properties'[Agreed_Price__c]),USERELATIONSHIP('Calendar'[Date],'Salesforce Properties'[Forecast_Closed_Date_Sales__c]))


RETURN XIRR('Asta',CALC2,'Calendar'[Date])

 

For reference:

 

Rolling XIRR Calculation

 

XIRR in differents periods

 

Solution to XIRR with Terminal Values

 

Best Regards,

Amy 

 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.