06-18-2020 15:34 PM
@az38 gets all the credit for this one. He noticed a peculiar behavior of MOD and reached out to me in my series on Excel to DAX Translation (L-M). Read the thread and original issue. The bottom line though is that MOD in DAX is broken. The good news I suppose is that it doesn't suck any worse than the Excel version. Now, there are some possible explanations:
Or you could point to the documentation on MOD and say that it specifies a return value of a whole number. But, let's be honest, that is all BS and garbage. There is no reason under the sun that MOD should not work with decimal numbers. Especially when the solution is right in the documentation:
The MOD function can be expressed in terms of the INT function: MOD(n, d) = n - d*INT(n/d)
Um, first of all, WRONG! Because it just so happens that the alternate version of expressing MOD actually WORKS!! So, you can't put that equivalency in your documentation because those two things do not equal one another. So to **bleep** with MOD! We don't need it!
ToHellwithMOD = VAR __Value = MAX('Table'[Value]) VAR __Divisor = [Divisor Value] RETURN __Value -__Divisor * INT( DIVIDE(__Value, __Divisor) )
thanks for sharing this. I just wanted to let you know that we are aware of the issue and we do have an item on the backlog for it. There is no ETA yet as we are dependent on some other teams.