Number.RoundDown
Rounds a number down to the next value at a set number of digits.
Number.RoundDown(number as nullable number, optional digits as number) as nullable numberReturns: The rounded number, or null when the input is null.
What it does
Number.RoundDown always moves a value down to the next figure at the digit you choose, regardless of the half-way point. Finance uses it when a rule allows only whole units or whole periods: full cases of product that can be shipped, whole months of rent that have been earned, or whole cents where a rule says fractions are not paid.
Syntax and arguments
- number: the value to round down.
- digits (optional): the number of decimal places to keep. The default is 0, which rounds down to a whole number.
Example
A warehouse can only ship full cases, so the number of cases that can be invoiced is the quantity rounded down. Partial cases stay in inventory.
let
Orders = #table(type table [Item = text, Units = number, UnitsPerCase = number],
{{"Widget A", 130, 12}, {"Widget B", 96, 12}}),
Cases = Table.AddColumn(Orders, "FullCases", each Number.RoundDown([Units] / [UnitsPerCase]), type number)
in
Cases
Widget A gives 130 divided by 12, which is about 10.8, and rounds down to 10 full cases. Widget B divides exactly into 8 cases. The ten leftover units of Widget A stay in inventory.
Example: a second, different use
Round a prepaid expense schedule down to whole months, so a month is only recognized when it is complete.
Things to watch
- It rounds in the direction of a smaller value. For negative numbers, test a sample row first, since the direction is easy to misread.
- Rounding down a rate or a per-unit price is rarely the right choice, because it understates the amount. Check the rule before you use it on money.
- A division that leaves a remainder is the usual reason to use it. Number.IntegerDivide gives the same whole part for positive numbers.
- Null in gives null out.
In business terms
Prepaid amortization, full cases and complete billing periods all need whole units. Rounding down in the query matches how the business actually recognizes or ships, so the report does not show revenue or stock that cannot be used yet.