Skip to main content
cancel
Showing results for 
Search instead 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

Reply
ashwinkolte
Helper I
Helper I

XIIR function in power BI

As we know below is the function for XIRR in power BI:
= XIRR(<tablename>, [Cashflow], [Date])

 

Consider the below table

Name of asset Col 1 (Invested amount)Col 2 (Withdrawal amount)Col 3 (Invested date)Col 4 (Withdrawal date)
Asset 1-1000001230001 Jan 202031 Dec 2022


How do I use the XIRR formula in Power BI for the above table given that we have 2 columns for Cashflow and 2 columns for date in the same row. Please note - I don’t want to unpivot the columns and align the amount and dates columns vertically.
So, the question is - how to use multiple columns for cashflow and multiple columns for dates in the Power BI XIRR formula, something equivalent to what can be easily done in excel e.g. =XIRR (Col1:Col2, Col3:Col4)

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

Hi @ashwinkolte ,

 

As far as I know, the calculation in Power BI is based on columns. So you need to transform amounts and dates into same columns in Power Query Editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwuTi1RMFTSUdI1NAABIMvQyBjKUPBKzFMwMjACcYwNFVxSk0E8I6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Name of asset" = _t, #" Col 1 (Invested amount)" = _t, #"Col 2 (Withdrawal amount)" = _t, #"Col 3 (Invested date)" = _t, #"Col 4 (Withdrawal date)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name of asset", type text}, {" Col 1 (Invested amount)", Int64.Type}, {"Col 2 (Withdrawal amount)", Int64.Type}, {"Col 3 (Invested date)", type date}, {"Col 4 (Withdrawal date)", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Renamed Columns" = Table.RenameColumns(#"Added Index",{{" Col 1 (Invested amount)", "Invested amount"}, {"Col 2 (Withdrawal amount)", "Withdrawal amount"}, {"Col 3 (Invested date)", "Invested date"}, {"Col 4 (Withdrawal date)", "Withdrawal date"}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Name of asset", "Invested date", "Withdrawal date", "Index"}, "Attribute", "Value"),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"Name of asset", "Index", "Attribute", "Value"}, "Attribute.1", "Value.1"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns1"," amount","",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value"," date","",Replacer.ReplaceText,{"Attribute.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Value1", each ([Attribute] = [Attribute.1])),
    #"Renamed Columns1" = Table.RenameColumns(#"Filtered Rows",{{"Value", "Amount"}, {"Value.1", "Date"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"Attribute.1"})
in
    #"Removed Columns"

New Table:

vrzhoumsft_0-1679639955467.png

Measure:

Measure = XIRR('Table','Table'[Amount],'Table'[Date])

Result is as below.

vrzhoumsft_1-1679639996057.png

 

Best Regards,
Rico Zhou

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

 

 

View solution in original post

3 REPLIES 3
ashwinkolte
Helper I
Helper I

Can we do this in DAX ?

Hi,

Yes, we can but we will have to unpivot first.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-rzhou-msft
Community Support
Community Support

Hi @ashwinkolte ,

 

As far as I know, the calculation in Power BI is based on columns. So you need to transform amounts and dates into same columns in Power Query Editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WciwuTi1RMFTSUdI1NAABIMvQyBjKUPBKzFMwMjACcYwNFVxSk0E8I6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Name of asset" = _t, #" Col 1 (Invested amount)" = _t, #"Col 2 (Withdrawal amount)" = _t, #"Col 3 (Invested date)" = _t, #"Col 4 (Withdrawal date)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name of asset", type text}, {" Col 1 (Invested amount)", Int64.Type}, {"Col 2 (Withdrawal amount)", Int64.Type}, {"Col 3 (Invested date)", type date}, {"Col 4 (Withdrawal date)", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Renamed Columns" = Table.RenameColumns(#"Added Index",{{" Col 1 (Invested amount)", "Invested amount"}, {"Col 2 (Withdrawal amount)", "Withdrawal amount"}, {"Col 3 (Invested date)", "Invested date"}, {"Col 4 (Withdrawal date)", "Withdrawal date"}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Renamed Columns", {"Name of asset", "Invested date", "Withdrawal date", "Index"}, "Attribute", "Value"),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"Name of asset", "Index", "Attribute", "Value"}, "Attribute.1", "Value.1"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns1"," amount","",Replacer.ReplaceText,{"Attribute"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value"," date","",Replacer.ReplaceText,{"Attribute.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Value1", each ([Attribute] = [Attribute.1])),
    #"Renamed Columns1" = Table.RenameColumns(#"Filtered Rows",{{"Value", "Amount"}, {"Value.1", "Date"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"Attribute.1"})
in
    #"Removed Columns"

New Table:

vrzhoumsft_0-1679639955467.png

Measure:

Measure = XIRR('Table','Table'[Amount],'Table'[Date])

Result is as below.

vrzhoumsft_1-1679639996057.png

 

Best Regards,
Rico Zhou

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

 

 

Helpful resources

Announcements
Europe Fabric Conference

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.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors