Forum Discussion

Nagesh20's avatar
Nagesh20
Icon for Helper I rankHelper I
2 years ago
Solved

Not finding the solution for XIRR error though all looks good

Hi, Please someone solve my query. I am not sure where i am missing the steps for generating IRR. I am strugling a lot for finding the right solution. Provided all the data below with calculations, t...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, Nagesh20 

    Maybe you can try to modify the DAX:

    Total Cash_NAV =
VAR currentdate = Table1[Date]
VAR Currentfun = Table1[Fund]
VAR filter_table = FILTER(Table1, Table1[Date] <= currentdate && Table1[Fund] = Currentfun)
VAR runtotal = CALCULATE(SUM(Table1[Daily NAV]), filter_table) -- Running Total
VAR _maxdate_per_fund = CALCULATE(MAX(Table1[Date]), FILTER(ALLSELECTED(Table1), Table1[Fund] = Currentfun)) -- Calculate max date per Fund
VAR filter_table2 = FILTER(Table1, Table1[Date] = currentdate && Table1[Fund] = Currentfun) -- Matching date with Fund name
VAR Total = -SWITCH(TRUE(),
    MAX(Table1[Date]) = _maxdate_per_fund, runtotal,
    MAX(Table1[Date]) < _maxdate_per_fund, 0,
    0
) + CALCULATE(SUM(Table1[Call]) - SUM(Table1[Distribution]), filter_table2) -- (-Ending NAV + Calls - Distribution)
RETURN
IF(Total = 0, 0.001, Total)

     If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)

     

    How to Get Your Question Answered Quickly 

    Best Regards

    Yongkang Hua

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

Nagesh20's avatar
Nagesh20
Icon for Helper I rankHelper I
2 years ago

Hi all,

 

Finally i found the solution for the below query using calculated column. Only one concern is left i.e. Ending NAV is taking based on max date in the date column rather than max date of the Fund (tried to figure out this mutliple times), resulting IRR error is getting for Fund C. Please someone fix this issue using colculated column than measure. I am almost close to the answer. Thanks in advance

 

 

Total Cash_NAV =
var currentdate=Table1[Date]
var Currentfun=Table1[Fund]
var filter_table=FILTER(Table1,Table1[Date]<=currentdate && Table1[Fund]=Currentfun)
var runtotal =CALCULATE(SUM(Table1[Daily NAV]),filter_table) -- Running Total
var _maxdate = CALCULATE(MAX(Table1[Date]),ALLSELECTED(Table1[Date])) -- for locking ending NAV from Running Total based on max date
var filter_table2=FILTER(Table1,Table1[Date]=currentdate && Table1[Fund]=Currentfun) -- matching date with Fund name
var
Total=
-SWITCH(TRUE(),
MAX(Table1[Date])=_maxdate,runtotal,
MAX(Table1[Date])<_maxdate,0,0) +CALCULATE(SUM(Table1[Call])-SUM(Table1[Distribution]),filter_table2) -- (-Ending NAV + Calls - Distribution)
RETURN
IF(Total=0,0.001,Total)