March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hi,
My objective is to calculated the XIRR as of 31 January 2023 using a measure. These are my measures
Purchase date of shares = MIN(Shares[Date of Purchase])
Cost of shares (Rs. cr) = -divide(SUM(Shares[Total cost of shares in Rs]),10^7)
Market value of shares (Rs. cr) = divide([Number of shares]*[MV per share],10^7)
In the Table visual when i drag Investee Company, these measures reuturn the following results
Purchase date of shares = 11 January 1999
Cost of shares = -1.41
Market value of shares = 397.12
In a slicer, i select the end date as 31 January 2023.
In MS Excel, the XIRR as of 31 January 2023, would be 26.41%.
How can i do this calculation in DAX via a measure.
Thank you for your help.
Solved! Go to Solution.
Hi @Greg_Deckler, @amitchandak, @Sahir_Maharaj , @lbendlin . Could you kinldy help me.
Thank you for your time.
Hi,
Thank you very much for your reply. That solution works.
Any idea why I got a different number?
Hi,
The date and month were interchanged.
D'oh! Sorry about that.
Hi @lbendlin ,
I am stuck at calculating the XIRR at the aggregate level. I have given a detailed description in the attached file. Could you kinldy modify my XIRR formula to get the correct figure at the aggregate level.
Thank you.
I don't understand why "Sale realisation of mutual funds (Rs. cr)" is blank for purchase_sale group 3. I tried to apply a SUMMARIZE filter to remove that row but that impacts the entire table contents. CALCULATETABLE will result in the same impasse of not being able to filter a table by a measure.
XIRR of mutual funds sold =
var a = CALCULATETABLE('Mutual funds',[Sale realisation of mutual funds (Rs. cr)]<>BLANK())
return
CALCULATE(XIRR(a,[Amt to be considered],[Transaction date of MF],,BLANK()),DATESBETWEEN('Calendar'[Date],MINX(ALL('Calendar'),'Calendar'[Date]),[Selected date]),USERELATIONSHIP('Mutual funds'[Transaction Date],'Calendar'[Date]))
Maybe this could be extended with the date ranges (at the moment it doesn't work):
XIRR of mutual funds sold =
var a = SUMMARIZE('Mutual funds',[Entity],[Scheme Name],[Purchase_sale group],"amt",[Amt to be considered],"td",[Transaction date of MF],"sd",[Sale realisation of mutual funds (Rs. cr)])
var b = filter(a,[sd]<>BLANK())
return
CALCULATE(XIRR(b,[amt],[td],,BLANK()),DATESBETWEEN('Calendar'[Date],MINX(ALL('Calendar'),'Calendar'[Date]),[Selected date]),USERELATIONSHIP('Mutual funds'[Transaction Date],'Calendar'[Date]))
I am guessing here, no idea what any of these financial terms mean.
Thank you for replying and giving it a go. I'll continue trying with the SUMMARIZE function.
Hi,
I have made some progress. I wrote the following 2 DAX formulas:
Calculated table formula
Table 2 = DATATABLE("Period",DATETIME,"CF",CURRENCY,{{"01/11/1999",-1.41},{"31/01/2023",397.12}})
Measure
XIRR of shares = XIRR('Table2',[CF],[Period])
I get the correct XIRR result i.e. 26.41%
The modification that i wish to bring here is that i would like to replace hard coded entries in the DATATABLE function with measures that i have written. I'd like
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
89 | |
86 | |
77 | |
49 |
User | Count |
---|---|
164 | |
149 | |
101 | |
73 | |
56 |