Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

IRR error

Dear All,   I want to calculate the IRR but when I try to I get an error. What do I do wrong?   I used the following code: ROLLING IRR = VAR minIndex = CALCULATE ( MIN ( 'TEST - Working Capi...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    Anonymous , I see need of one change, if this a measure then we need max in place of earlier

     

    ROLLING IRR =
    VAR minIndex =
    CALCULATE ( MIN ( 'TEST - Working Capital'[Index] ), ALL ( 'TEST - Working Capital' ) )
    RETURN
    IF (
    'TEST - Working Capital'[Index] > minIndex && NOT(ISBLANK('TEST - Working Capital'[Data])),
    CALCULATE (
    XIRR (
    'TEST - Working Capital',
    'TEST - Working Capital'[Cash flow amount],
    'TEST - Working Capital'[Transaction Date]
    ),
    FILTER (
    allselected('TEST - Working Capital'),
    MAx ( 'TEST - Working Capital'[Index] ) >= 'TEST - Working Capital'[Index]
    )
    )
    )

     

    Trying to check in a file

amitchandak's avatar
amitchandak
Super User
4 years ago

Anonymous , I see need of one change, if this a measure then we need max in place of earlier

 

ROLLING IRR =
VAR minIndex =
CALCULATE ( MIN ( 'TEST - Working Capital'[Index] ), ALL ( 'TEST - Working Capital' ) )
RETURN
IF (
'TEST - Working Capital'[Index] > minIndex && NOT(ISBLANK('TEST - Working Capital'[Data])),
CALCULATE (
XIRR (
'TEST - Working Capital',
'TEST - Working Capital'[Cash flow amount],
'TEST - Working Capital'[Transaction Date]
),
FILTER (
allselected('TEST - Working Capital'),
MAx ( 'TEST - Working Capital'[Index] ) >= 'TEST - Working Capital'[Index]
)
)
)

 

Trying to check in a file

Anonymous's avatar
Anonymous
Not applicable
4 years ago

amitchandak , thanks that was the bug. Looked at it too long to see the error.

I want to make this a 'Rolling IRR' based on different projects. So that he changes the IRR everyday day per project. How  should I change the formula then?

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , I tried out, Seem like for me only working combination are

     

    for grand total/card

    XIRR (
    ('TEST - Working Capital'),
    calculate(Sum('TEST - Working Capital'[Cash flow amount])),
    'TEST - Working Capital'[Transaction Date]
    )

     

    or

     

    XIRR (
    allselected('TEST - Working Capital'),
    calculate(Sum('TEST - Working Capital'[Cash flow amount])),
    'TEST - Working Capital'[Transaction Date]
    )

     

    refer to this video for around 6 Min, and check the suggestion. if that can help

    https://everyday.cc/powerbi/learning/videos/curbal-dax-fridays-142-irr-xirr-and-xnpv-in-dax/