CEILING DAX Function (Math and Trig)

Rounds a number up, to the nearest integer or to the nearest unit of significance.

Syntax

CEILING ( <Number>, <Significance> )

Parameter	Attributes	Description
Number		The value you want to round.
Significance		The multiple to which you want to round.

Return values

Scalar A single value of one these types: integer, decimal, currency.

The number is rounded as specified. The return data type is usually of the same type of the significant argument, with the following exceptions:

If the number argument type is Currency, the return type is Currency.
If the significance argument type is Boolean, the return type is Integer.
If the significance argument type is non-numeric, the return type is Decimal.

Remarks

There are two CEILING functions in DAX, with the following differences:

The CEILING function emulates the behavior of the CEILING function in Excel.
The ISO.CEILING function follows the ISO-defined behavior for determining the ceiling value.

The two functions return the same value for positive numbers, but different values for negative numbers. When using a positive multiple of significance, both CEILING and ISO.CEILING round negative numbers upward (toward positive infinity). When using a negative multiple of significance, CEILING rounds negative numbers downward (toward negative infinity), while ISO.CEILING rounds negative numbers upward (toward positive infinity).

Examples

= CEILING  ( 10.2, 1 )                   -- Returns 11     (Integer)
= CEILING  ( 10.7, 1 )                   -- Returns 11     (Integer)
= CEILING  ( 10.2, 0.5 )                 -- Returns 10.5   (Decimal)
= CEILING  ( 10.7, 0.5 )                 -- Returns 11     (Decimal)
= CEILING  ( 10.2, CURRENCY ( 0.5 ) )    -- Returns 10.5   (Currency)
= CEILING  ( 10.7, CURRENCY ( 0.5 ) )    -- Returns 11     (Currency)
= CEILING  ( -10.2, 1 )                  -- Returns -10    (Integer)
= CEILING  ( -10.2, -1 )                 -- Returns -11    (Integer)
= CEILING  ( -10.7, 1 )                  -- Returns -10    (Integer)
= CEILING  ( -10.7, -1 )                 -- Returns -11    (Integer)
= CEILING  ( -10.2, 0.5 )                -- Returns -10    (Decimal)
= CEILING  ( -10.7, 0.5 )                -- Returns -10.5  (Decimal)
= CEILING  ( -10.2, CURRENCY ( 0.5 ) )   -- Returns -10    (Currency)
= CEILING  ( -10.7, CURRENCY ( 0.5 ) )   -- Returns -10.5  (Currency)

Related functions

Other related functions are:

ISO.CEILING

Last update: Feb 6, 2019 » Contribute » Show contributors

Contributors: Alberto Ferrari, Marco Russo

MSDN documentation: https://msdn.microsoft.com/en-us/query-bi/dax/ceiling-function-dax