HELP with XIRR function (aggregate data)

[Anonymous]

Hi there,

I am struggling with XIRR, I think I am loosing the fight so help is welcomed 🙂

I have build a table "DataFin" (from a copy of another source table and then adding some filtering and pivoting data) like the following :

Notice that DIM_Date is date of the file and not related at all to Date of the cashflow.

DIM_DATE    DIM_COUNTRY   DIM_FILE ... (other DIMS) ... Index   Payment    Date

01/01/2018    Belgium                123                                          0           -1000          01/05/2018

01/01/2018    Belgium                123                                          1           150             01/06/2018

01/01/2018    Belgium                123                                          ...           ...                ...

02/01/2018    Belgium                345                                          0           -500          01/07/2018

02/01/2018    Belgium                345                                          1           75             01/08/2018

02/01/2018    Belgium                345                                          ...           ...                ...

And then I created a simple XIRR measure as follows :

TEC = XIRR(DataFin ; [Payment] ; [Date])

This measure gives (good) results as soon as I am just analyzing TEC by file 

DIM_FILE   TEC

123             3 %

345             3.99 %

Aggregation also works well when I ommit DIM_FILE in the visual but keep filtering on that dimension on the visual.

TEC

3.57 %

PROBLEM IS  : as soon as I try to use any slicers (DIM_DATE ...) or deactivate my test filter on the files or add dimension in the visual, it always gives me the following error :

THANKS

[Anonymous · Accepted solution]

Thanks Sam.

Here is a solution I found using groupby combined with IFERROR for missing values:

TEC =
IFERROR(XIRR(GROUPBY(DataFin;DataFin[Index];"Payment";SUMX(CURRENTGROUP();DataFin[Payment]);"Date_Payment";MAXX(CURRENTGROUP();DataFin;[Date])) ; [Payment] ; [Date_Payment]);"")

[v-chuncz-msft]

@Anonymous,

Check if the following posts help.

• https://community.powerbi.com/t5/Desktop/XIRR-function-cannot-find-solution/td-p/415658
• https://community.powerbi.com/t5/Desktop/XIRR-function-error-in-a-PowerBI-model/td-p/302254