Daily DAX : Day 137 DB

DB Function in DAX

Syntax:

DAX

DB(<cost>, <salvage>, <life>, <period>[, <month>])

Explanation:

The DB function calculates the depreciation of an asset for a specific period using the fixed-declining balance method. This method applies a fixed depreciation rate to the asset’s remaining book value each period, resulting in higher depreciation amounts early in the asset’s life and lower amounts later. It’s an accelerated depreciation technique, often used for assets that lose value more quickly upfront, like machinery or vehicles.

    <cost>: The initial cost of the asset (a numeric value).

    <salvage>: The value of the asset at the end of its depreciation (salvage value), which can be zero.

    <life>: The number of periods (e.g., years) over which the asset is depreciated (its useful life).

    <period>: The specific period (e.g., year) for which you want to calculate depreciation. Must use the same units as <life> (e.g., if <life> is in years, <period> is a year number between 1 and <life>).

    <month> (optional): The number of months in the first year of depreciation (1–12). If omitted, it defaults to 12, assuming a full year.

How It Works:

The fixed-declining balance method computes depreciation using this formula internally:

    Rate = 1 - ((salvage / cost) ^ (1 / life)), rounded to three decimal places.

    Depreciation for a period = (cost - total depreciation from prior periods) * rate.

    For the first and last periods, adjustments are made if <month> is less than 12 (partial-year depreciation).

This means the depreciation amount decreases each period as it’s applied to the declining book value (cost minus accumulated depreciation).

Use Case:

Suppose a company buys equipment for $10,000 with a salvage value of $1,000 and a useful life of 5 years. You want to know the depreciation for Year 1.

Example:

DAX

DepreciationYear1 = DB(10000, 1000, 5, 1)

    Cost: $10,000

    Salvage: $1,000

    Life: 5 years

    Period: 1 (Year 1)

    Month: Omitted (defaults to 12)

The result might be approximately $3,600 (the exact value depends on the calculated rate, typically around 36% for this scenario). In Year 2, you’d use:

DAX

DepreciationYear2 = DB(10000, 1000, 5, 2)

This would yield a lower amount (e.g., ~$2,304), reflecting the declining balance.

Practical Application:

    Financial Reporting: Calculate depreciation for assets to match their usage patterns (e.g., heavy equipment that wears out faster early on).

    Tax Planning: Accelerated depreciation can reduce taxable income more in early years.

    Asset Management: Track depreciation schedules in Power BI dashboards.

Notes:

    The DB function is ideal for fixed-declining balance but differs from the double-declining balance method (handled by the DDB function, which uses a factor like 2x the straight-line rate).

    It’s not supported in DirectQuery mode for calculated columns or row-level security, so use it in measures or imported data models.