Forum Discussion
Help with XIRR, SELECTEDVALUE and Matrix table with multiple columns values
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.
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:
| 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
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
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.
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]))returnResultMany thanks,
Markus