Forum Discussion

Helper III

3 years ago

Solved

XIRR help needed

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) Co...

Need Help

Anonymous
3 years ago
Hi ashwinkolte ,

Without unpivotting columns, it's difficult.

But if you just don't want to unpivot columns in Power Query, how about unpivotting in Power BI Desktop using DAX?

Please kindly refer to

DAX Unpivot - Microsoft Power BI Community

And below are the measure and the result:

Measure = var _table= UNION( SELECTCOLUMNS('Table',"Name",[Name of asset],"Cashflow",[Col 1 (Invested amount)],"Date",[Col 3 (Invested date)]), SELECTCOLUMNS('Table',"Name",[Name of asset],"Cashflow",[Col 2 (Withdrawal amount)],"Date",[Col 4 (Withdrawal date)]) ) return XIRR(_table,[Cashflow],[Date])

Best Regards,

Stephen Tao

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

Anonymous

Not applicable

3 years ago

Hi ashwinkolte ,

Without unpivotting columns, it's difficult.

But if you just don't want to unpivot columns in Power Query, how about unpivotting in Power BI Desktop using DAX?

Please kindly refer to

DAX Unpivot - Microsoft Power BI Community

And below are the measure and the result:

Measure = var _table=

    UNION(
        SELECTCOLUMNS('Table',"Name",[Name of asset],"Cashflow",[Col 1 (Invested amount)],"Date",[Col 3 (Invested date)]),
        SELECTCOLUMNS('Table',"Name",[Name of asset],"Cashflow",[Col 2 (Withdrawal amount)],"Date",[Col 4 (Withdrawal date)])
   
    )


    return XIRR(_table,[Cashflow],[Date])

Best Regards,

Stephen Tao

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

sample324.pbix68 KB