XIRR DAX Help

[dmbd1904]

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=

(-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('Calendar',CALC2,'Calendar'[Date])

[v-xicai]

Hi   @dmbd1904 ,

Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[dmbd1904 · Accepted solution]

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]

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.

[amitchandak]

@dmbd1904 , refer if this can help

https://www.thebiccountant.com/2017/05/23/goal-seeking-and-xirr-in-powerbi-and-powerquery/