cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Super User

## Calculate XIRR

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
1 ACCEPTED SOLUTION
Super User

Not sure why I get 27.44% but here's my version.

10 REPLIES 10
Super User

Hi @Greg_Deckler@amitchandak@Sahir_Maharaj , @lbendlin .  Could you kinldy help me.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

Not sure why I get 27.44% but here's my version.

Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

Any idea why I got a different number?

Super User

Hi,

The date and month were interchanged.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

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.

Super User

Thank you for replying and giving it a go.  I'll continue trying with the SUMMARIZE function.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

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

1. 01/11/1999 to be replaced with the measure [Purchase date of shares]
2. -1.41 to be replaced with the measure [Cost of shares (Rs. cr)]
3. 31/01/2023 to be replaced with the measure [Selected period]
4. 397.12 to be replaced with the measure [Market value of shares (Rs. cr)]
How can i do this?

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors