MOD
Returns the remainder after a number is divided by a divisor.
MOD(<number>, <divisor>)Returns: A number (a scalar), with the same sign as the divisor.
What it does
MOD returns what is left over after one number is divided by another. MOD(10, 3) is 1, because 3 goes into 10 three times with 1 left over. Finance uses it for cycles, such as month positions within a fiscal year, and for leftover amounts after full units or full batches. The result takes the sign of the divisor.
Syntax and arguments
- number: the value to divide.
- divisor: the number to divide by. It cannot be zero.
Example
The company's fiscal year starts in July. The model needs a fiscal month number from 1 to 12 for each calendar date, so period reports can sort the year correctly.
Fiscal Month =
MOD(MONTH('Date'[Date]) - 7, 12) + 1
July gives 1 and June gives 12. Subtracting 7 moves the start of the year, MOD keeps the answer between 0 and 11, and adding 1 makes it a count from 1. Reports can then sort by fiscal month.
Example: a second, different use
Units left over after full cases are packed, so the warehouse knows what needs a partial case:
Loose Units = MOD(Sales[SalesQuantity], 12)
An order of 25 units has 1 loose unit after two full cases of 12. Loose units can be shown as a separate line on the packing list.
Things to watch
- MOD takes its sign from the divisor. MOD(-3, 2) is 1, not -1. Test negative cases if your data can contain them.
- A divisor of zero is an error. Guard the formula when the divisor can be blank or zero.
- MOD works on whole remainders. For decimal remainders, the result is still the leftover after the division, which may not be what a reader expects.
- Use MOD with INT when you want the number of full units, and MOD for what is left.
- MOD of a date is not meaningful. Convert the date to a month or day number first.
In business terms
MOD appears in fiscal calendars, recurring payment schedules, every nth record sampling for audit, packing and allocation rules, and check digit style validation of account numbers. It gives a cycle position that a plain division cannot.