Daily DAX : Day 218 CEILING

 The CEILING function in Power BI DAX rounds a number up to the nearest multiple of a specified value (significance). It’s useful for scenarios where you need to align numbers to specific increments, such as pricing, inventory, or time intervals.

Syntax

dax

CEILING(<number>, <significance>)

    number: The value to round up.

    significance: The multiple to which the number is rounded up. Must be a positive number, and its sign must match the number’s sign.

How It Works

    CEILING rounds up (away from zero) to the nearest multiple of the significance.

    If the number is already a multiple of significance, no rounding occurs.

    If significance is omitted or zero, an error occurs.

Example

dax

CEILING(3.7, 1)  // Returns 4 (rounds 3.7 up to the nearest multiple of 1)

CEILING(3.7, 0.5)  // Returns 4 (rounds 3.7 up to the nearest multiple of 0.5)

CEILING(-3.7, -1)  // Returns -4 (rounds -3.7 up to the nearest multiple of -1)

CEILING(10, 3)  // Returns 12 (rounds 10 up to the nearest multiple of 3)

Use Cases

    Pricing Adjustments:

        Round up product prices to the nearest dollar or cent for consistency.

        dax

        RoundedPrice = CEILING(19.23, 1)  // Returns 20

    Inventory Management:

        Round up quantities to match packaging units (e.g., boxes of 10 items).

        dax

        OrderUnits = CEILING(47, 10)  // Returns 50 (orders in multiples of 10)

    Time Intervals:

        Round up time durations to the nearest hour or minute for scheduling.

        dax

        RoundedHours = CEILING(2.3, 1)  // Returns 3 hours

    Financial Calculations:

        Round up loan payments or interest to the nearest cent.

        dax

        Payment = CEILING(123.456, 0.01)  // Returns 123.46

Notes

    Use FLOOR for rounding down or ROUND for standard rounding.

    Ensure the signs of number and significance match to avoid errors.

    For negative numbers, CEILING rounds away from zero (e.g., CEILING(-3.2, 1) returns -4).

This function is handy in Power BI for creating clean, standardized measures or calculated columns in reports.