cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

## 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 with Selectedvalue =

var EndDate = SELECTEDVALUE('Tab_SelectedDates'[DateS]) //e.g. 2019/12/31 & 2020/12/31

var StartDate = Date(year(EndDate)-1,12,31) // ->2018/12/31 & 2019/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)) //Combining all three CF segments Start,Op&End

var IRR= CALCULATE(XIRR(CF_Union,[Wert],[Date]))
//Calculating  IRR (not working for multiple DateS values, but works correct with any single DateS values)

var AVG_VALUES=CALCULATE(Average([Wert]))
//This alternative calulation of Average instead of IRR works perfectly for multiple DateS!?

return

IRR

In the Matrix table with DateS as columns and the measure as values  I would expect something like

 2019-31-12 2020-31-12 5% 6%

However there is an error message for the matrix table

Can’t display the visual

Details: Couldn’t load the data for this visual
MdxScript(Model)(39,20) Calcuclation error in measure XIRR Yearly
A result for XIRR couldn’t be determined because the Values and Dates arguments are empty.

But if I filter to a single date with a slicer, the same matrix table calculates correctly 5% and 6% for each single filtered date.

So in principle the XIRR in the matrix table works fine for a single filtered DateS (and the underlying data is fine)

But it does not work with multiple DateS in the columns.
It must be a specific issue with the XIRR function, because if I use any other simpler function like Average ([Wert]) it all works, i.e. showing the average for multiple DateS columns.

Any suggestions? I am wondering if the filtering is causing the XIRR formula issues?

1 ACCEPTED SOLUTION
Community Support

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.

4 REPLIES 4
Community Support

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.

Regular Visitor

Dear Neeko @v-tangjie-msft ,

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:

 Date Category Wert 31.12.2008 Value 100 31.12.2009 Value 101 31.12.2010 Value 102 01.03.2009 CF 1 01.06.2009 CF 1 01.09.2009 CF 2 01.03.2010 CF 1 01.06.2010 CF 1 01.09.2010 CF 3

This transforms into CF_Start like

 Date Category Wert 31.12.2008 Value -100 31.12.2009 Value -101 31.12.2010 Value -102

CF_End like

 Date Category Wert 31.12.2008 Value 100 31.12.2009 Value 101 31.12.2010 Value 102

and CF_Op like

 Date Category Wert 01.03.2009 CF 1 01.06.2009 CF 1 01.09.2009 CF 2 01.03.2010 CF 1 01.06.2010 CF 1 01.09.2010 CF 3

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

 Date Category Wert 31.12.2008 Value -100 01.03.2009 CF 1 01.06.2009 CF 1 01.09.2009 CF 2 31.12.2009 Value -102

-->IRR~5%

and

 Date Category Wert 31.12.2009 Value -101 01.03.2010 CF 1 01.06.2010 CF 1 01.09.2010 CF 3 31.12.2010 Value 102

-->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

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

Community Support

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.

Regular Visitor

Dear Neeko, @v-tangjie-msft

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors