IN-K Excel to DAX Translation

[Greg_Deckler]

This is part of a series of posts on Excel to DAX translation. The main article is here.

INDEX	N/A
INDIRECT	N/A
INFO	N/A
INT	INT
INTERCEPT	INTERCEPT = VAR __XBar = AVERAGEX('Table',[x]) VAR __YBar = AVERAGEX('Table',[y]) VAR __Slope = VAR __Table = ADDCOLUMNS( 'Table', "Numerator",([x] - __XBar) * ([y] - __YBar), "Denominator",POWER([x] - __XBar,2) ) RETURN DIVIDE( SUMX(__Table,[Numerator]), SUMX(__Table,[Denominator]) ) RETURN __YBar - __Slope * __XBar
INTRATE	INTRATE or DIVIDE([Redemption] - [Investment],[Investment) * DIVIDE([DaysInYear],([Maturity] - [Settlement])*1.) DaysInYear is either 360 for 0, 2 or 4, 365 for 3 or the actual days in the year (365 or 366) for 1
IPMT	IPMT
IRR	IRR = VAR __Values = 'Table' VAR __Table = ADDCOLUMNS( GENERATESERIES(-1,1,.000001), "__NPV", VAR __Table = ADDCOLUMNS( __Values, "__NPV",[Value]/POWER((1+EARLIER([Value])),[Index]) ) RETURN SUMX(__Table,[__NPV]) ) VAR __MaxNPV = MINX(FILTER(__Table,[__NPV]>0),[__NPV]) VAR __MinNPV = MAXX(FILTER(__Table,[__NPV]<0),[__NPV]) VAR __MaxRate = MAXX(FILTER(__Table,[__NPV] = __MaxNPV),[Value]) VAR __MinRate = MAXX(FILTER(__Table,[__NPV] = __MinNPV),[Value]) RETURN __MaxRate + ( ( (0 - __MaxNPV) / (__MinNPV - __MaxNPV) ) * (__MinRate - __MaxRate) ) or XIRR
ISBLANK	ISBLANK
ISERR	ISERROR
ISERROR	ISERROR
ISEVEN	ISEVEN
ISFORMULA	N/A
ISLOGICAL	ISLOGICAL
ISNA	N/A
ISNONTEXT	ISNONTEXT
ISNUMBER	ISNUMBER
ISODD	ISODD
ISREF	N/A
ISTEXT	ISTEXT
ISO.CEILING	ISO.CEILING
ISOWEEKNUM	WEEKNUM(...,...,21)
ISPMT	ISPMT
JIS	N/A
KURT	Kurtosis