How to calculate xirr in pbi

[Archit]

Hi I am new to power bi and not sure what I am doing wrong here

I am trying to calculate XIRR for cashflow and dates present in "table1" by defining a measure. The measure creates a variable table "Data" by selecting columns from "table 1" another variable table is to for closing date and final amount - "closing_date" and then third variable is union of above two variables. Following is the code

XIRR =

VAR Data = UNION(SELECTCOLUMNS(table1, "abc", [Cashflow]),SELECTCOLUMNS(table1, "xyz", [ Date]))

VAR Closing_Date = ROW("abc", TODAY(),"xyz",SUM(table1[Total value]))

VAR Final_table = UNION(Data,Closing_Date)

RETURN XIRR(Final_table, [xyz], [abc])

I am receiving the error "Each table argument of 'UNION' must have the same number of columns." What am I doing wrong here? Is there a better way to do this?

[v-yanimei-msft · Accepted solution]

Hi @Archit  , 

Thank you for reaching out with your query on calculating XIRR in Power BI. Based on your question, I have analyzed the following reasons.

Receiving the error "Each table argument of 'UNION' must have the same number of columns." is due to the `UNION` function requiring both tables to have the same number of columns. In your code, the `Data` variable and the `Closing_Date` variable likely do not have the same number of columns, which is causing the error.

To resolve this, ensure that both tables produced by the `SELECTCOLUMNS` function and the `ROW` function have the same number of columns. For instance:

VAR Data = UNION(

    SELECTCOLUMNS(table1, "CashflowDate", [Date], "CashflowAmount", [Cashflow]),

    SELECTCOLUMNS(table1, "CashflowDate", TODAY(), "CashflowAmount", SUM(table1[Total value]))

)

This example assumes you want to combine cash flow dates and amounts with today's date and the total value. You can adjust the column names and calculations as necessary for your dataset. If you want to learn more about UNION, click UNION function (DAX) - DAX | Microsoft Learn.

If you're still facing issues or have specific questions about your dataset, could you provide more details about the structure of "table1" and the exact outcome you're aiming for? Understanding more about your data and your goal will help in offering a more tailored solution.

Best Regards,

Caroline Mei

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

[Archit]

Thank you, this worked for me. I made a change as table 1 contained multiple rows so second select column was adding equal number of rows in the Data table. Used a DISTINCT function before it to add unique value. Final code is 

XIRR = VAR Data = UNION(

    SELECTCOLUMNS(table1, "CashflowDate", [Date], "CashflowAmount", [Cashflow]),

    DISTINCT(SELECTCOLUMNS(table1, "CashflowDate", TODAY(), "CashflowAmount", SUM(table1[Total value])))

)

RETURN XIRR(Data, [CashflowAmount], [CashflowDate])

Thank you for your help!

[123abc]

The error message you're encountering, "Each table argument of 'UNION' must have the same number of columns," indicates that the tables you are trying to union together do not have the same structure, i.e., they don't have the same number of columns.

In your code, the tables you're trying to union are from different sources:

1. The Data table contains two columns: "abc" and "xyz" from table1.
2. The Closing_Date table contains two columns: "abc" and "xyz", but "xyz" is a date column, whereas "abc" seems to be a scalar value of today's date and the sum of the "Total value" column.

Hence, the UNION function is unable to combine them due to the mismatch in the number of columns.

It seems you're trying to add a single row to your data for today's date and the sum of the "Total value" column. You should construct the Closing_Date table differently, without using the SUM function within the ROW function.

Here's a revised version of your code:

XIRR =
VAR Data = SELECTCOLUMNS(table1, "abc", [Cashflow], "xyz", [Date])

VAR Closing_Date = ROW("abc", TODAY(), "xyz", CALCULATE(SUM(table1[Total value]), ALL(table1)))

VAR Final_table = UNION(Data, {Closing_Date})

RETURN
XIRR(Final_table, [xyz], [abc])

Changes made:

1. Removed the UNION of two SELECTCOLUMNS since you can select multiple columns in a single SELECTCOLUMNS call.
2. Revised the Closing_Date table construction. Instead of using SUM within ROW, used CALCULATE to sum the "Total value" column in the context of all rows of table1.
3. Used curly braces to create a single-row table for Closing_Date so that it can be directly appended to Data without using UNION on two different tables.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Archit]

I tried this, however the error i am receiving is "The expression refers to multiple coloumns. Multiple coloumns cannot be converting to a scalar value". Maybe I am not using the calculate function correctly. 

Thanks for replying