Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

XIRR on a dynamic union table

Hi,

 

I am tryning to calculate the XIRR function on a dynamic union table. I have a table with cashflows for 5 entities, all since the investment starting point up until last date in the PBI. At the same time I have a measure, calculating the current potential cash inflow, depending on the date I select.

 

I wrote this DAX script:

 

Unioned IRRtable working =
var yr = SELECTEDVALUE('Calendar'[Year])
var mh = SELECTEDVALUE('Calendar'[Month Order])
var max_dt = DATE(yr, mh, 1)
var entity = SELECTEDVALUE('Entity Sector'[Entity])
var valuation = [Current Cash acquisition / current cash selling price]

VAR _dividends =
SUMMARIZECOLUMNS('Valuation Dividends'[Entity], 'Valuation Dividends'[Month], 'Valuation Dividends'[Year], 'Valuation Dividends'[Value], 'Valuation Dividends'[Date], 'Valuation Dividends'[Initial],filter('Valuation Dividends', 'Valuation Dividends'[Date] <= max_dt), FILTER('Valuation Dividends', 'Valuation Dividends'[Entity] = entity))

VAR _currentvaluation =
CALCULATETABLE(ADDCOLUMNS(selectCOLUMNS('Entity Sector', "Entity", 'Entity Sector'[Entity]), "Month", mh, "Year", yr, "Value", valuation, "Date", max_dt, "Initial", 0), FILTER('Entity Sector', 'Entity Sector'[Entity]=entity))

VAR _master_table =
UNION( _dividends, _currentvaluation)

RETURN

XIRR(_master_table, [Value], [Date])
 
 
The problem is, when I put the table creation dax as a separate table and create separate measure to calculate XIRR on this table it works. But I want the table to be dynamic, i.e. take only cashflows until my selected date and the valuation from my selected date. And in such case i have an error saying:
 

 

Need Help
Tutorial Requests
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , Try to change SUMMARIZECOLUMNS like

     

    SUMMARIZECOLUMNS('Valuation Dividends'[Entity], 'Valuation Dividends'[Month], 'Valuation Dividends'[Year], 'Valuation Dividends'[Value], 'Valuation Dividends'[Date], 'Valuation Dividends'[Initial],filter('Valuation Dividends', 'Valuation Dividends'[Date] <= max_dt && 'Valuation Dividends'[Entity] = entity))

     

     

    or

    SUMMARIZE(filter('Valuation Dividends', 'Valuation Dividends'[Date] <= max_dt && 'Valuation Dividends'[Entity] = entity),'Valuation Dividends'[Entity], 'Valuation Dividends'[Month], 'Valuation Dividends'[Year], 'Valuation Dividends'[Value], 'Valuation Dividends'[Date], 'Valuation Dividends'[Initial])

     

     

    and check

     

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , Try to change SUMMARIZECOLUMNS like

     

    SUMMARIZECOLUMNS('Valuation Dividends'[Entity], 'Valuation Dividends'[Month], 'Valuation Dividends'[Year], 'Valuation Dividends'[Value], 'Valuation Dividends'[Date], 'Valuation Dividends'[Initial],filter('Valuation Dividends', 'Valuation Dividends'[Date] <= max_dt && 'Valuation Dividends'[Entity] = entity))

     

     

    or

    SUMMARIZE(filter('Valuation Dividends', 'Valuation Dividends'[Date] <= max_dt && 'Valuation Dividends'[Entity] = entity),'Valuation Dividends'[Entity], 'Valuation Dividends'[Month], 'Valuation Dividends'[Year], 'Valuation Dividends'[Value], 'Valuation Dividends'[Date], 'Valuation Dividends'[Initial])

     

     

    and check