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.