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]
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
|
Be the first to comment