cancel
Showing results for
Did you mean: ## L-M Excel to DAX Translation

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

 LARGE function LARGE Quick Measures Gallery LCM function LCM LEFT, LEFTB functions LEFT LEN, LENB functions LEN LINEST function LN function LN LOG function LOG LOG10 function LOG10 LOGEST function LOGINV function EXP([mean]+[stdev]*NORM.S.INV([probability])) LOGNORM.DIST function PDF = 1/([x]*[stdev]*SQRT(2*PI()))* EXP(-1*POWER((LN([x])-[mean]),2)/(2*POWER([stdev],2)))   CDF = NORM.S.DIST((LN([x]) - [mean])/[stdev],TRUE) LOGNORMDIST function PDF = 1/([x]*[stdev]*SQRT(2*PI()))* EXP(-1*POWER((LN([x])-[mean]),2)/(2*POWER([stdev],2)))   CDF = NORM.S.DIST((LN([x]) - [mean])/[stdev],TRUE) LOGNORM.INV function EXP([mean]+[stdev]*NORM.S.INV([probability])) LOOKUP function LOOKUPVALUE LOWER function LOWER MATCH function N/A MAX function MAX or MAXX MAXA function MAXA MAXIFS function MAXX(FILTER(... && ...)...) or CALCULATE(MAX(),FILTER(... && ...)) MDETERM function MDURATION function Macaulay Duration Quick Measures Gallery MEDIAN function MEDIAN or MEDIANX MID, MIDB functions MID MIN function MIN or MINX MINIFS function MINX(FILTER(... && ...)...) or CALCULATE(MIN(),FILTER(... && ...)) MINA function MINA MINUTE function MINUTE MINVERSE function MIRR function ``````MIRR = VAR __Table = 'Table' VAR __FRate = [Finance_rate] VAR __RRate = [Reinvestment_rate] VAR __MIRRNum = VAR __Max = MAXX(FILTER(__Table,[Value]>0),[Index]) VAR __Table = ADDCOLUMNS( FILTER(__Table,[Value]>0), "NPV",[Value] * POWER(1 + __RRate,__Max - [Index]) ) RETURN SUMX(__Table,[NPV]) VAR __MIRRDenom = VAR __Table = ADDCOLUMNS( FILTER(__Table,[Value]<0), "NPV",[Value]/POWER((1+__FRate),[Index]) ) RETURN SUMX(__Table,[NPV]) * (1+__FRate) RETURN POWER(-1 * __MIRRNum/__MIRRDenom,1/(COUNTROWS(__Table)-1)) - 1`````` MMULT function MMULT Quick Measures Gallery MOD function MOD MODE function Mode Single Column Quick Measures Gallery Mode Multi-Column Quick Measures Gallery MODE.MULT function Mode Single Column Quick Measures Gallery Mode Multi-Column Quick Measures Gallery MODE.SNGL function Mode Single Column Quick Measures Gallery Mode Multi-Column Quick Measures Gallery MONTH function MONTH MROUND function MROUND MULTINOMIAL function MULTINOMIAL Quick Measures Gallery MUNIT function MUNIT Quick Measures Gallery thanks for such a great guide!

what do you know about MOD() function?

Maybe it is not an issue at all? @az38 Well, what I can tell you is that the behavior you are seeing is exactly the behavior of Excel's MOD function. Wolfram Alpha returns 0 (see below). So, there is something wrong with Excel and DAX's MOD function with decimal numbers.

My understanding of modulus is that it is supposed to return the remainder. As you point out, there should be no remainder dividing 1.2 by .2.  Here is something interesting @az38 , in the documentation for the Excel MOD function:

https://support.office.com/en-us/article/mod-function-9b6cd169-b6ee-406a-a97b-edf2a9dc24f3

and DAX documentation on MOD:

https://docs.microsoft.com/en-us/dax/mod-function-dax

They provide the alternative formula for MOD:

The MOD function can be expressed in terms of the INT function: MOD(n, d) = n - d*INT(n/d)

If you use that formula, you get the correct results, 0 for even numbers like 1.2!!!

So.... Here is some more information, I am not sure if these apply but they seem related potentially:

Top Kudoed Posts
Latest Articles