Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculating IRR using slicer and projected data

Hello   I am trying to calculate the projected IRR for a particular company, i.e. the IRR on a particular date in the future. I have two relevent data tables that I am using, one that provides the ...
Slicer
XIRR
  • johnt75's avatar
    johnt75
    4 years ago

    You could add in the future date and value by creating a variable as the UNION of the base data and ROW for the future projections, something along the lines of

    Rate of return =
VAR tmpTable =
    UNION (
        SELECTCOLUMNS (
            FILTER ( 'Base Table', 'Base Table'[Some column] = "filter value" ),
            "_Date", 'Base Table'[Date],
            "_Value", 'Base Table'[Value]
        ),
        ROW (
            "_Date", SELECTEDVALUE ( 'Future Dates'[Date] ),
            "_Value", [Projected future value measure]
        )
    )
RETURN
    XIRR ( tmpTable, [_Value], [_Date] )
johnt75's avatar
johnt75
Icon for Super User rankSuper User
4 years ago

You could add in the future date and value by creating a variable as the UNION of the base data and ROW for the future projections, something along the lines of

Rate of return =
VAR tmpTable =
    UNION (
        SELECTCOLUMNS (
            FILTER ( 'Base Table', 'Base Table'[Some column] = "filter value" ),
            "_Date", 'Base Table'[Date],
            "_Value", 'Base Table'[Value]
        ),
        ROW (
            "_Date", SELECTEDVALUE ( 'Future Dates'[Date] ),
            "_Value", [Projected future value measure]
        )
    )
RETURN
    XIRR ( tmpTable, [_Value], [_Date] )
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thanks for the suggestion. The principle of this works well and the table created works with the XIRR function, so I am half way there. 

     

    However, for the additional ROW I am struggling to work out a way for the user to be able to select a particular date in the future (currently trying to use a slicer) and then associate a value with that date, in order to use this projected valuation as part of IRR calculation. 

     

    I have tried, for example, to use this measure:

    Selected_year = 
        CALCULATE(
        MAX( Calendar[Date] ),
        ALLSELECTED( Calendar[Date] )

     

    to link a slicer to the DAX you suggest, but the _date in the new ROW is unresponsive and just uses the last date. 

     

    Are you able to suggest a measure to use for _date and _value for the new ROW that would allow the user to select a time period and the respective value would be used?

     

    • johnt75's avatar
      johnt75
      Icon for Super User rankSuper User
      4 years ago

      Create a separate date table which is not connected to anything, just to use for the slicer. That's what I intended the 'Future Dates' table to be in my initial code.

      For the value to use, you could use a what if parameter. That automatically creates a measure which returns the selected value, so you can just use that