Forum Discussion

markusb73's avatar
markusb73
Regular Visitor
2 years ago
Solved

Help with XIRR, SELECTEDVALUE and Matrix table with multiple columns values

Hello, I am having problems to show XIRRs for a set of multiple dates with SELECTEDVALUE. The same IRR calculation for any single date however works fine!   The code is as follow: XIRR Yearly wit...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi markusb73 ,

     

    Try it:

    XIRR Yearly with Selectedvalue =

var EndDate = LASTDATE('Tab_SelectedDates'[DateS]) 

var StartDate = Date(year(EndDate)-1,12,31) 
 

var CF_Start=CALCULATETABLE('Tab_StartValues','Tab_StartValues'[Date]=StartDate)

var CF_Op=CALCULATETABLE('Tab_CFValues', 'Tab_CFValues'[Date]>StartDate && 'Tab_CFValues'[Date]<EndDate)

var CF_End=CALCULATETABLE('Tab_EndValues','Tab_EndValues'[Date]=EndDate)

var CF_Union=CALCULATETABLE(union(CF_Start,CF_Op,CF_End)) 

var IRR= CALCULATE(XIRR(CF_Union,[Wert],[Date]))

    Best Regards,

    Neeko Tang

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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi markusb73 ,

 

It seems like the issue might be related to the fact that the XIRR function requires two arguments: an array of cash flows and an array of dates. In your code, you are using the CALCULATETABLE function to create tables for the cash flows and dates, but it's possible that the SELECTEDVALUE function is not returning the expected values for the dates when multiple dates are selected. One suggestion would be to use the VALUES function instead of SELECTEDVALUE to get a list of all the selected dates, and then use that list to create the table of dates for the XIRR function. You could try something like this: 

 

 

``` var SelectedDates = VALUES('Tab_SelectedDates'[DateS]) 
var DatesTable = FILTER('Tab_CFValues', 'Tab_CFValues'[Date] IN SelectedDates) 
var CF_Union = UNION('Tab_StartValues', DatesTable, 'Tab_EndValues') 
var IRR = XIRR(CF_Union, [Wert], [Date]) ```

 

For more information, please refer to How To Use Power BI VALUES Function (DAX) - Enterprise DNA

 

Best Regards,

Neeko Tang

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

  • markusb73's avatar
    markusb73
    Regular Visitor
    2 years ago

    Dear Neeko Anonymous ,

    many thanks for you idea, however I think it does not apply/work in this case.

     

    Lets explain by the data, e.g. the base data as follows:

    DateCategoryWert
    31.12.2008Value100
    31.12.2009Value101
    31.12.2010Value102
    01.03.2009CF1
    01.06.2009CF1
    01.09.2009CF2
    01.03.2010CF1
    01.06.2010CF1
    01.09.2010CF3

     

    This transforms into CF_Start like

    DateCategoryWert
    31.12.2008Value-100
    31.12.2009Value-101
    31.12.2010Value-102

     

    CF_End like

    DateCategoryWert
    31.12.2008Value100
    31.12.2009Value101
    31.12.2010Value102

     

    and CF_Op like

    DateCategoryWert
    01.03.2009CF1
    01.06.2009CF1
    01.09.2009CF2
    01.03.2010CF1
    01.06.2010CF1
    01.09.2010CF3

     

    The to be calculated Cashflows for the 1 year IRRs for the End_Dates 2019/12/31 & 2020/12/31 using union are therefore:

    DateCategoryWert
    31.12.2008Value-100
    01.03.2009CF1
    01.06.2009CF1
    01.09.2009CF2
    31.12.2009Value-102

    -->IRR~5%

    and

    DateCategoryWert
    31.12.2009Value-101
    01.03.2010CF1
    01.06.2010CF1
    01.09.2010CF3
    31.12.2010Value102

    -->IRR~6%

     

    And for a single filtered end date the calculation works fine, but not for multiple i.e.>=2 selectedvalue dates in a matrix table

    Regarding your suggestion:

    The due dates=end dates for the IRRs to be calculated are not the same as the relevant CF_Op dates.
    The CF_Op dates are >StartDate and <EndDate and are actually 3 dates in this example, and not identical to the selected end dates

     

    Maybe the issue is the double filter condition in
    var CF_Op=CALCULATETABLE('Tab_CFValues', 'Tab_CFValues'[Date]>StartDate && 'Tab_CFValues'[Date]<EndDate)
    combined with XIRR & union

    and you approach using IN Relevant_dates would work, but I have not figured out how to do this in this context.

    But many thanks for your suggestion.

    Kind regards,
    Markus

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi markusb73 ,

       

      Try it:

      XIRR Yearly with Selectedvalue =

var EndDate = LASTDATE('Tab_SelectedDates'[DateS]) 

var StartDate = Date(year(EndDate)-1,12,31) 
 

var CF_Start=CALCULATETABLE('Tab_StartValues','Tab_StartValues'[Date]=StartDate)

var CF_Op=CALCULATETABLE('Tab_CFValues', 'Tab_CFValues'[Date]>StartDate && 'Tab_CFValues'[Date]<EndDate)

var CF_End=CALCULATETABLE('Tab_EndValues','Tab_EndValues'[Date]=EndDate)

var CF_Union=CALCULATETABLE(union(CF_Start,CF_Op,CF_End)) 

var IRR= CALCULATE(XIRR(CF_Union,[Wert],[Date]))

      Best Regards,

      Neeko Tang

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

      • markusb73's avatar
        markusb73
        Regular Visitor
        2 years ago

        Dear Neeko, Anonymous 

        thanks a lot for your help, yes LASTDATE does work now
        (like I imagined Selectedvalue should work but did not)

        Although I do not really understand why, but this might be due to my low DAX experience and the XIRR complexities.

        I however also found out that the XIRR union part also works with simple filter instead of calculate table, see below (the naming is a bit different from the original post)

         

        XIRR Union =

        var EndDate = Lastdate('Tab_A _Datums'[DatumS])
        var StartDate = Date(year(EndDate)-1,12,31)
        var Result=Calculate(XIRR(union
            (
            Filter('Tab_A MWS','Tab_A MWS'[Datum]=StartDate),
            Filter('Tab_A CF', 'Tab_A CF'[Datum]>StartDate && 'Tab_A CF'[Datum]<EndDate),
            Filter('Tab_A MWE','Tab_A MWE'[Datum]=EndDate)
            )
            ,[Wert],[Datum]))
            return
            Result
         
        Many thanks,
        Markus