Power Pivot Principles: The A to Z of DAX Functions – AMORLINC
15 March 2022
In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions. This week, we look at AMORLINC.
The AMORLINC function
The AMORLINC function in DAX is similar to the last function we discussed, AMORDEGRC. The difference is that this function returns the depreciation for each accounting period, as provided for the French accounting system. If an asset is purchased in the middle of the accounting period, the pro-rated depreciation is taken into account instead.
AMORLINC uses the following syntax:
AMORLINC(cost, date_purchased, first_period, salvage, period, rate, [basis])
Important: Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2022, 5, 23) for the 23rd day of May, 2022. Problems can occur if dates are entered as text.
The AMORLINC function syntax has the following arguments:
- cost: this is required. This represents the cost of the asset
- date_purchased: also required. The date of the purchase of the asset
- first_period: required. The date of the end of the first period
- salvage: required. The salvage value at the end of the life of the asset
- period: required. The period in question
- rate: required. The rate of depreciation
- basis: optional. The year basis to be used.
Some notes to remember on how the basis argument works (there’s no number 2):
| Basis | Date system |
|---|---|
| 0 or omitted | 360 days (NASD method) |
| 1 | Actual |
| 3 | 365 days in a year |
| 4 | 360 days in a year (European method) |
It should be noted that:
- dates are stored as sequential serial numbers so they can be used in calculations. In DAX, December 30, 1899 is day 0, and January 1, 2008 is 39448 because it is 39,448 days after December 30, 1899
- period and basis are rounded to the nearest integer
- an error is returned if:
- cost < 0
- first_period or date_purchased is not a valid date
- date_purchased > first_period
- salvage < 0 or salvage > cost
- period < 0
- rate ≤ 0
- basis is any number other than 0, 1, 3 or 4
- this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
Please see my example below:
| Description | Value |
|---|---|
| Cost | $2,400 |
| Date purchased | 19 August 2008 |
| End of first period | 31 December 2008 |
| Salvage value | $300 |
| Period | 1 |
| Depreciation rate | 15% |
| Actual basis (see above) | 1 |
For this data, we may write the following code:
For the first period’s depreciation, given the conditions presented, the value returned will be $360.
Come back next week for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.
