Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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]);"")
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]);"")
@Anonymous,
Check if the following posts help.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |