Skip to content
Tutorials Center
My learning

Topics

Lesson 33 of 56 ยท DAX

DAX Trigonometric, Financial and Bitwise Functions

Learn the specialist DAX functions: SIN, SINH, ACOT, DEGREES, SLN, SYD, DDB, DB, COUPDAYS and BITAND, with formulas and runnable Python stand-ins.

Most reports need only sums, ratios and dates. But DAX also carries a long tail of specialist functions that show up in engineering dashboards, finance models and access-control logic: trigonometric functions for angles and cycles, financial functions for depreciation and bond coupons, and bitwise functions that read flags packed into a single number. You will not use them daily. You do need to know they exist, what their arguments mean, and where they behave differently from what you might guess.

Power BI cannot run on this site, so every formula below is read-only. After each group you get a Python stand-in that reproduces the same maths, so you can check the numbers by hand before trusting a measure. The first half builds on DAX Math and Statistical Functions; the examples assume a table Parts or Assets where the columns are named in the formulas.

Trigonometric functions

DAX uses radians, not degrees, for every trigonometric function. A right angle is PI() / 2, not 90. Two helpers convert between the units.

DAX
Angle in Radians = RADIANS ( [AngleDegrees] )
Angle in Degrees = DEGREES ( [AngleRadians] )
Circle Area      = PI () * POWER ( [Radius], 2 )
Half Circle      = SQRTPI ( [Area] )          // SQRT ( [Area] * PI () )
Group Functions Notes
Basic SIN, COS, TAN The argument is an angle in radians.
Inverse ASIN, ACOS, ATAN ASIN and ACOS need a value from -1 to 1; the result is in radians.
Cotangent COT, ACOT COT(x) is 1 / TAN(x). ACOT returns a value between 0 and pi.
Hyperbolic SINH, COSH, TANH, COTH Used for curves such as a hanging cable (the catenary) and in some growth models.
Inverse hyperbolic ASINH, ACOSH, ATANH, ACOTH ACOSH needs a value of at least 1; ATANH needs a value between -1 and 1 (exclusive); ACOTH needs a value whose absolute value is above 1.
Helpers PI, RADIANS, DEGREES, SQRTPI PI() returns 3.14159265358979.

There is no SEC, CSC or COSEC in DAX. If you need a cosecant, use DIVIDE ( 1, SIN ( [Angle] ) ). DAX also has no ISEVEN or ISODD (those are Excel functions): test parity with MOD ( [Number], 2 ) = 0, or round up to the next even or odd number with EVEN and ODD.

A classic use is the distance between two points on the Earth, for a map report that finds the nearest store. The haversine formula needs SIN, COS, ASIN, SQRT and RADIANS:

DAX
Distance km =
VAR R    = 6371
VAR dLat = RADIANS ( [Lat2] - [Lat1] )
VAR dLon = RADIANS ( [Lon2] - [Lon1] )
VAR a =
    POWER ( SIN ( dLat / 2 ), 2 )
        + COS ( RADIANS ( [Lat1] ) ) * COS ( RADIANS ( [Lat2] ) )
            * POWER ( SIN ( dLon / 2 ), 2 )
RETURN
    2 * R * ASIN ( SQRT ( a ) )
Python Stand-in: radians, hyperbolic functions and the haversine distance

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

SINH(1) is about 1.1752 and COSH(1) about 1.5431. ACOSH(0.5) fails in Python and returns an error in DAX, because the function is only defined from 1 upwards. Test the input with IF or wrap the call in IFERROR when the data may contain values outside the domain.

Financial functions: depreciation

Accounting spreads the cost of an asset over its useful life. DAX provides the same depreciation functions as Excel. The arguments always use the same names: cost is the purchase price, salvage is the value at the end of life, life is the number of periods the asset lasts, and period says which period you want (in the same unit as life).

DAX
Straight Line   = SLN ( [Cost], [Salvage], [Life] )
Sum of Years    = SYD ( [Cost], [Salvage], [Life], [Period] )
Double Declining = DDB ( [Cost], [Salvage], [Life], [Period] )
Fixed Declining = DB ( [Cost], [Salvage], [Life], [Period], 12 )
Function Method Result per period
SLN Straight line The same amount every period: (cost - salvage) / life.
SYD Sum of the years' digits Falls by a fixed step each period; early years are larger.
DDB Double-declining balance (the factor, default 2, can be changed) A fixed percentage of the remaining book value, never below the salvage value.
DB Fixed-declining balance A fixed rate (rounded to three decimals) applied to the remaining value; the optional fifth argument is the number of months in the first year (default 12).
AMORLINC, AMORDEGRC French accounting systems Take purchase and first-period dates plus a rate; they depreciate in proportion to the days owned.

AMORLINC(cost, date_purchased, first_period, salvage, period, rate, [basis]) and AMORDEGRC(...) take the same arguments, but AMORDEGRC multiplies the rate by a coefficient that depends on the asset's life (1.5, 2 or 2.5). They exist for businesses that must follow French rules, so most models never use them. SLN, SYD, DDB and DB cover nearly every other case.

For a machine that costs 30,000, has a salvage value of 7,500 and lasts 10 years, SLN gives 2,250 a year. SYD for year 1 is 22,500 times 10 times 2 divided by 10 times 11, which is 4,090.91; year 10 is only 409.09. The Python stand-in makes the difference visible.

Python Stand-in: four depreciation methods side by side

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

All three methods write off exactly cost - salvage (22,500) over the ten years, but at different speeds. DDB takes 6,000 in year 1, and in year 7 it is capped so the book value stops at the salvage value: after that it returns 0. Use the method your accountants specify, never the one that gives the nicest chart.

Financial functions: bond coupons

A bond pays interest at fixed dates called coupons. The family of COUP functions describes the schedule around a settlement date (when you buy) and a maturity date (when the bond ends). All of them take settlement, maturity, frequency (1 = yearly, 2 = half-yearly, 4 = quarterly) and an optional day-count basis:

Function Returns
COUPPCD The coupon date before the settlement date.
COUPNCD The coupon date after the settlement date.
COUPNUM The number of coupons still to be paid between settlement and maturity.
COUPDAYBS Days from the previous coupon to the settlement date.
COUPDAYS Days in the coupon period that contains the settlement date.
COUPDAYSNC Days from the settlement date to the next coupon.
DAX
Days Since Coupon =
COUPDAYBS ( DATE ( 2011, 1, 25 ), DATE ( 2011, 11, 15 ), 2, 1 )

The basis argument chooses how days are counted: 0 or omitted is the US 30/360 method, 1 is actual days in an actual year, 2 is actual/360, 3 is actual/365 and 4 is the European 30/360. The accrued interest you owe the seller is the coupon amount times COUPDAYBS / COUPDAYS, so these functions feed straight into bond pricing. If the dates are not valid or the settlement is not before the maturity, the functions return an error.

Python Stand-in: a coupon schedule on the actual/actual basis

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

That result is the one Microsoft's documentation uses for the same dates: 71 days since the last coupon, a 181-day period and 110 days to the next coupon. The 15 November maturity falls in the second half-year; stepping back six months at a time finds the 15 November 2010 and 15 May 2011 coupons around the settlement date.

Bitwise functions

A bitwise function works on the binary digits of a whole number. A few older systems pack several yes/no settings (permissions, features, status flags) into one number, and these functions unpack it.

Function Meaning
BITAND(a, b) A bit is 1 in the result only if it is 1 in both numbers.
BITOR(a, b) A bit is 1 if it is 1 in either number.
BITXOR(a, b) A bit is 1 if it is 1 in exactly one of the numbers.
BITLSHIFT(n, s) Shifts the bits of n left by s places, which multiplies by 2 to the power s.
BITRSHIFT(n, s) Shifts right by s places, which divides by 2 to the power s and drops the remainder.

All of them require non-negative whole numbers below 2 to the power 48, and a negative shift amount reverses the direction. A common pattern is a permission mask: Read = 1, Write = 2, Delete = 4, and a user's permissions are stored as the sum.

DAX
Can Write   = BITAND ( [Permissions], 2 ) > 0
Add Delete  = BITOR ( [Permissions], 4 )
Toggle Read = BITXOR ( [Permissions], 1 )
Times Eight = BITLSHIFT ( [Value], 3 )
Python Stand-in: permission flags with bitwise operators

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

If you control the source system, store the flags as separate yes/no columns instead. They are far easier for report authors to filter and understand. Bitwise functions are for data you cannot change.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In which unit does DAX expect the angle for SIN, COS and TAN?

    All DAX trigonometric functions use radians. Use RADIANS to convert degrees and DEGREES to convert back.
  2. 2. Which expression gives the cosecant of an angle in DAX?

    DAX has no cosecant function. The cosecant is 1 divided by the sine.
  3. 3. A machine costs 30,000, has a salvage value of 7,500 and lasts 10 years. What does SLN return?

    Straight-line depreciation is (cost - salvage) / life = 22,500 / 10 = 2,250 per year.
  4. 4. Which function returns the number of days between the previous coupon date and the settlement date of a bond?

    COUPDAYBS counts days from the beginning of the coupon period to the settlement date. COUPDAYSNC counts to the next coupon and COUPDAYS is the length of the whole period.
  5. 5. A permissions column holds 6 (binary 110). What does BITAND([Permissions], 2) > 0 test?

    BITAND keeps only the bits that are on in both numbers. 6 AND 2 is 2, so the flag with value 2 is set.

Summary

  • DAX trigonometry works in radians; RADIANS, DEGREES and PI convert and supply constants. There is no cosecant or secant, and no ISEVEN.
  • Inverse and hyperbolic functions have limited domains (for example ACOSH needs 1 or more); out-of-range inputs return errors.
  • SLN, SYD, DDB and DB are the standard depreciation methods; AMORLINC and AMORDEGRC follow French rules.
  • The COUP family describes a bond's coupon schedule around a settlement date, with a day-count basis.
  • BITAND, BITOR, BITXOR, BITLSHIFT and BITRSHIFT read flags packed in whole numbers; prefer separate columns when you own the data.

Next: DAX Table Functions.