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 ,

 

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