Data Analysis Expressions is referred to as DAX in Power BI. It is an expressive language for formulas that are also utilized in Power BI and other visualization tools. It is utilized to create many kinds of formulae. We have all been utilizing various Excel functions in MS Excel for a very long time. DAX is comparable to Excel’s Insert Function. The many formula types used for data modeling Power BI are written in DAX.
DAX Financial Function
Financial functions in DAX are used in formulas that do financial calculations, such as net present value and rate of return. These capabilities are analogous to the financial capabilities of Microsoft Excel. Let’s exercise using the DAX COUPDAYBS Financial Function with a reseller’s Dim Date type dataset. Below is a screenshot of the dataset:
DAX COUPDAYBS
DAX COUPDAYBS reveals how many days there are between the start of a coupon period and its settlement date.
Syntax: COUPDAYBS(<settlement>, <maturity>, <frequency>[, <basis>])
- settlement: The settlement date for the security. The day after the issuance date when the security is traded to the buyer is known as the settlement date for securities.
- maturity: The maturity date of the security. The security’s expiration date is known as the maturity date.
- frequency: The quantity of annual coupon payments. The frequency for annual payments is 1, for semi-annual payments is 2, and for quarterly payments is 4.
- basis: The kind of day count foundation to employ (optional). Basis is taken to be 0 if it is omitted. Below this table is a list of the acceptable values.
The following values are valid for the basis parameter:
| Basis | Day Count Basis |
|---|---|
| 0 or omitted | US (NASD) 30/360 |
| 1 | Actual/actual |
| 2 | Actual/360 |
| 3 | Actual/365 |
| 4 | European 30/360 |
Output
The amount of time between the start of a coupon period and its settlement date. The subsequent DAX command:
Example: Measure = EVALUATEANDLOG(COUPDAYBS(DATE(2011,1,25), DATE(2011,11,15), 2, 1))
Returns, for a bond with the aforementioned terms, the number of days between the start of the coupon period and the settlement date.
DAX COUPDAYS
DAX COUPDAYS gives the total number of days that make up the coupon term, including the settlement date.
Syntax: COUPDAYS(<settlement>, <maturity>, <frequency>[, <basis>])
- settlement: The settlement date for the security. The day after the issuance date when the security is traded to the buyer is known as the settlement date for securities.
- maturity: The maturity date of the security. The security’s expiration date is known as the maturity date.
- frequency: The quantity of annual coupon payments. The frequency for annual payments is 1, for semi-annual payments is 2, and for quarterly payments is 4.
- basis: The kind of day count foundation to employ (optional). Basis is taken to be 0 if it is omitted. Below this table is a list of the acceptable values.
The following values are valid for the basis parameter:
| Basis | Day Count Basis |
|---|---|
| 0 or omitted | US (NASD) 30/360 |
| 1 | Actual/actual |
| 2 | Actual/360 |
| 3 | Actual/365 |
| 4 | European 30/360 |
Output
How many days there are in the coupon period that includes the settlement date will be told. The subsequent DAX command:
Example: Measure = EVALUATEANDLOG(
{
COUPDAYS(DATE(2011,1,25), DATE(2011,11,15), 2, 1)
})
Returns, for a bond with the aforementioned terms, the number of days in the coupon period that include the settlement date.
DAX COUPDAYSNC
The number of days between the settlement date and the following coupon date is returned.
Syntax: COUPDAYSNC(<settlement>, <maturity>, <frequency>[, <basis>])
- settlement: The settlement date for the security. The day after the issuance date when the security is traded to the buyer is known as the settlement date for securities.
- maturity: The maturity date of the security. The security’s expiration date is known as the maturity date.
- frequency: The quantity of annual coupon payments. The frequency for annual payments is 1, for semi-annual payments is 2, and for quarterly payments is 4.
- basis: The kind of day count foundation to employ (optional). Basis is taken to be 0 if it is omitted. Below this table is a list of the acceptable values.
The following values are valid for the basis parameter:
| Basis | Day Count Basis |
|---|---|
| 0 or omitted | US (NASD) 30/360 |
| 1 | Actual/actual |
| 2 | Actual/360 |
| 3 | Actual/365 |
| 4 | European 30/360 |
Output
How many days there are between the settlement date and the following coupon date are obtained. The subsequent DAX command:
Example: Measure = EVALUATEANDLOG(
{
COUPDAYSNC(DATE(2011,1,25), DATE(2011,11,15), 2, 1)
})
Returns, for a bond with the aforementioned terms, the number of days between the settlement date and the next coupon date.
Note:
- In order to be used in calculations, dates are recorded as consecutive serial numbers. Since January 1, 2008, is 39,448 days after December 30, 1899, it is represented in the DAX as day 0, and December 30, 1899, as day 0.
- The date a buyer purchases a coupon, such as a bond, is known as the settlement date. A coupon’s expiration date is known as the maturity date. For instance, let’s say a buyer purchases a 30-year bond six months after it is issued on January 1, 2008. The maturity date would be January 1, 2038, which is 30 years from the issuance date of January 1, 2008, and the settlement date would be July 1, 2008.
- Settlement and maturity are rounded to the nearest integer.
- The nearest integer is used to round the frequency and basis.
- An error is returned if:
- Settlement or maturity dates are not acceptable.
- The establishment is greater than or equal to maturity.
- Any number other than 1, 2, or 4 is considered to be frequent.
- The basis is either less than 0 or greater than 4.
- This function is not supported for use in Direct Query mode when used in calculated columns or row-level security (RLS) rules.