DAX financial functions: Depreciation calculations

The series so far:

1. DAX financial functions: Loan calculations
2. DAX financial functions: Depreciation calculations

This article continues the introduction to the DAX financial functions begun in DAX financial functions: Loan calculations. As noted there, these 50-plus functions debuted around the time of the July 2020 release of Power BI Desktop; they are largely derived from those found in Excel, and so will seem familiar to many Excel users. In the first article, you gained exposure to a group of related loan- / investment-related functions. In this article, the focus will be upon another group, Depreciation and Amortization functions.

You’ll be working from the hypothetical scenario presented in the first article: You have a client who has contacted you to ask for an introduction to the new DAX financial functions, preferring quick overviews for the most popular, based upon widespread use of their Excel counterparts. Because both you and the client have agreed that a “lunch-and-learn” format will be most accessible to the handful of Power BI authors in their accounting and finance department, you have parceled your introductions to the functions over short sessions, each of which will group a few related functions together, with practice examples based upon a small dataset with which client attendees can follow along and create straightforward calculations with the new functions.

The Depreciation and Amortization group of DAX finance functions introduced in this article are used to calculate depreciation, and they are regularly called upon in financial / accounting (including, of course, tax) analysis and reporting. The more common depreciation methods, for which a function is in place at this writing, are covered here, including the following (with the name of the associated method shown):

• SLN() – Straight-line
• SYD() – Sum-of-years digits
• DB() – Fixed declining balance
• DDB() – Double-declining balance / other

Illustration 1: The Focus in this Article: A Group of Functions Used to Calculate Depreciation / Amortization

NOTE: Keep in mind that the DAX depreciation functions return the depreciation value for the specified period(s) only. Accumulated depreciation, net book value, and other derivative values can be easily calculated from this “periodic expense” value, but require additional steps. In addition to using the DAX depreciation functions to calculate the periodic depreciation values, Power BI is an excellent tool for generating these derivative values. The values can be easily determined in conjunction with consulting GAAP (Generally Accepted Accounting Principles), your local accounting policies and local, state and Federal statutes, depending upon the immediate need(s).

As a part of this introduction, you’ll have an opportunity to examine how each function can be employed to support business requirements of the sort that your hypothetical colleagues encounter routinely, and, for the most part, accomplish with Microsoft Excel, in meeting regular business requirements. You’ll learn the purpose of each function and understand the steps that each function takes in achieving the objectives of the depreciation method that it enacts, “under the covers” within the function. You will then undertake a practice example with each depreciation function that demonstrates how it interacts with a small asset data set, via a calculation that you construct.

Moreover, you will:

• Examine the syntax involved in exploiting the function.
• Undertake an illustrative example of the use of the function in a practice exercise.
• Briefly discuss the results you obtain via the steps of the practice example.

Preparation for the Practice Exercises in this Article

Assuming that you have installed Power BI Desktop (the illustrations in this article reflect the December 2020 release), you are ready to download and open the sample Power BI Desktop file. You will use the file for hands-on practice with the concepts introduced in the sections that follow.

NOTE: The latest version of Power BI is available for free download at www.powerbi.com.

Download and Open the Sample Power BI File (.pbix) for Use in this Article

The small sample Power BI file you’ll be using contains enough imported data to support practice exercises for the functions covered in this article. You’ll add the calculations and visualizations upon which the article focuses as you go. Using the sample dataset provided will ensure that the results you obtain in following the exercises’ detailed steps agree to the results obtained (and depicted) as you progress through the individual sections.

Once the sample .pbix file is downloaded, take the following steps to open it in Power BI Desktop.

1. Open Power BI Desktop.
2. Select File – Open other reports from the splash dialog that appears upon entry, as shown.

Illustration 2: Select Open Other Reports on the Splash Dialog that Appears

3. Navigate to the downloaded .pbix file.

Illustration 3: Select the Downloaded File and Open …

4. Click Open.

The .pbix file opens, and you arrive within the Report view, which consists of a single tab containing a blank canvas. As many of you are aware, you can tell you are in the Report view because the current view (of the three views available in the upper left corner, Report, Data, and Model) is indicated by the yellow bar to the left of the icon.

5. Click the Data view icon along the left of Power BI Desktop (underneath the Report view icon), as desired, to become familiar with the basic sample model, which includes three rows of asset data.

At this point, you’ll construct a table visualization to contain asset details, to which you will need to be able to easily add a depreciation calculation using a DAX financial function in the practice example for the first (Straight-line) depreciation type. This table will also serve as a model for practice examples for the subsequent depreciation functions.

Construct a Table Visualization to Contain Basic Asset Data

1. In the sample Power BI model, make sure to be in the Report view.
2. Click the cursor in the upper half of the blank canvas.
3. Click the Table icon in the collection atop the Visualizations tab, to create a blank table on the canvas.

Illustration 4: Create a New Table Visualization on the Canvas

4. Ensuring that the above table visualization is selected, add the following fields (from the Assets table in the Fields pane) to the Values section of the Fields tab of the Visualizations pane:
   • PurchDate
   • AssetID
   • AssetName
   • Cost
   • Salvage
   • Life

The fields appear in the table as depicted.

Illustration 5: Additions in the Values Section of the Fields Tab

Finally, it’s a good idea to label the table you’re creating – as I’ve said throughout my Stairway to DAX and Power BI series and elsewhere. This is a minor point, but, as multiple visualizations tend to accumulate within a development environment, it’s often helpful to make them easily distinguishable via descriptive, “working” titles. It’s also a great way to identify the “at-a-glance” verification mechanism for other internal team members to use, say, in granting approval to promote a model and its contents to production from development.

5. With the new table selected, once again, click the Format (“paint roller”) tab, underneath the visualizations collection atop the Visualizations pane.
6. Scroll down to the Title section of the Format settings.
7. Click the Title slider to On.
8. Expand the Title section by clicking the carat to the left of the Title label.
9. Type Asset Details: Straight-Line Depreciation (z_SLN) into the Title text box.
10. Set formatting as desired (you can see what I used in the illustration below).

The settings for the Title section of the Format tab appear, alongside the new table, somewhat as depicted.

Illustration 6: Title Settings for the New Table

You now have a basic Asset Depreciation table that will serve as a template container for each DAX depreciation function introduced throughout the practice session below. This will provide a combined view of the relevant factors involved in the use of each function, as well as a comparative look at the different values the calculations generated.

Before proceeding, you can speed preparation for the practice exercises by cloning a table similar to the one above for each of the three additional DAX depreciation functions we will be examining. The calculation set we create for every function taken up within this article (except for the Straight-line Depreciation function taken up first) generates a different depreciation value for each period, requiring a separate calculation for each period, as you’ll see. But the initial table you have created contains the “common core” of all the tables created in this article. And working with a set of clones of that table will be more efficient than creating all from scratch.

Create a “Clone” of the Table Visualization Created Above for Each of the Remaining DAX Depreciation Functions within the Practice Example

You can use a quick “copy and customize” approach to create a separate table to house each of your upcoming practice examples.

1. Click the table you created above on the canvas to select it.
2. Select CTRL+C (“Copy”) to copy the existing table visualization.
3. Click outside the table and onto the blank canvas.
4. Select CTRL + V (“Paste”) on the keyboard to create an identical copy of the table you just created.
5. Repeat Steps 3 and 4 above two more times, for a total of three times, to create three copies of the same table.

You now have four identical copies of the original table visualization.

6. Turn on Gridlines (under the View tab on the main menu) if you find it useful in arranging visualizations.
7. Move the newly created (at this point identical) copies to align them evenly, below the original, on the canvas, to create working space, approximately as shown.

Illustration 7: “Four Tables for Four Practice Sets:” Copies below the Original …

Now, all that remains is to customize each of the templates you have cloned so that the DAX depreciation function to be demonstrated in each is reflected in its title. The good news is that the existing title is already formatted, and needs only a modification in the description of the function it will contain, as you’ll see in the next steps.

1. With the second table from the top selected, click the Format tab, once again, underneath the visualizations collection atop the Visualizations pane.
2. Scroll down to the Title section of the Format settings, as you did earlier with the original table visualization created.
3. Expand the Title section by clicking the carat to the left of Title label, if necessary.
4. Change the Title from Asset Details: Straight-Line Depreciation (z_SLN) to Asset Details: Sum-of-years Digits Depreciation (z_SYD).
5. Leave formatting and other settings within the Title section as they were set in the original.

The second clone table now appears, above the original table, as depicted.

Illustration 8: The Second Clone Becomes the SYD Calculations Table …

You’ll next customize a table to house the Fixed Declining Balance calculation you will craft.

1. With the third table from the top selected, click the Format tab, once again, underneath the visualizations collection atop the Visualizations pane.
2. Scroll down to the Title section of the Format settings, as you did within each of the earlier two table visualizations.
3. Expand the Title section by clicking the carat to the left of Title label, as required.
4. Change the Title from Asset Details: Straight-Line Depreciation (z_SLN) to Asset Details: Fixed Declining Balance Depreciation (z_DB).
5. Leave formatting and other settings within the Title section as set in the original.

The third clone table now appears, above the original table, as depicted.

Illustration 9: The Third Clone Becomes the DB Calculations Table …

Finally, you’ll create a table to house the Double-Declining Balance calculation you will write.

1. With the fourth table from the top selected, click the Format tab, as before, underneath the visualizations collection atop the Visualizations pane.
2. Scroll down to the Title section of the Format settings, as you did within each of the earlier three table visualizations.
3. Expand the Title section by clicking the carat to the left of Title label, as needed.
4. Change the Title from Asset Details: Straight-Line Depreciation (z_SLN) to Asset Details: Double-Declining Balance Depreciation (z_DDB).
5. Leave formatting and other settings within the Title section as set in the original.

The fourth clone table now appears, above the original table, as depicted.

Illustration 10: The Fourth Clone Becomes the DDB Calculations Table …

You’re now ready to begin putting the DAX depreciation functions to work within the practice steps for each below.

Shared Parameters for DAX Financial Functions for Depreciation

The financial functions introduced in this article all share the same objective: To support the computation of depreciation. While the accounting rules / assumptions contained within each function differ, they largely draw upon the same arguments / parameters as a basis upon which to apply their respective rules and calculate depreciation.

To gain an introduction to the operation of the functions efficiently, you’ll work through a separate practice exercise for each. Because the basic arguments share three common arguments, it makes sense to explain the shared arguments / parameters here, so as not to repeat them in the Syntax section of each function. Should you need to refresh your understanding of any given argument’s meaning, you need only refer to the below.

The shared arguments / parameters are:

• Cost – The initial cost of the asset. From a capitalization perspective, Cost typically includes purchase price, together with transportation / shipping, setup and preparation costs, taxes, etc. What can be included depends upon accounting / tax statutes, policies and procedures in effect for the owner.

• Salvage – The value at the end of the depreciation. Also known as the “salvage value” of the asset, this takes into account what the asset, at the end of its economic life, might still be worth, or sold / exchanged for.

Example: A truck that was initially put on a company’s books at $22,000, has reached the end of its economic life after five years, based upon organizational accounting and tax policy. At this time, the vehicle is determined to have a “blue book” / market value of $600, which might be described as the “remaining,” or “salvage” value.

• Life – The number of periods over which the asset is depreciated (sometimes called the “economic” or “useful” life of the asset).

You’ll work with the individual functions in the sections that follow.

DAX Financial Function: SLN()

According to the Data Analysis Expressions (DAX) Reference, the SLN() function “returns the straight-line depreciation of an asset for one period.”

Straight line depreciation is the simplest way of calculating the depreciation of an asset. The depreciation amount is the same (hence “straight-line) over each period of the asset’s life. SLN() returns the periodic depreciation allowance based upon the values you supply it.

Example: The truck mentioned earlier, put on a company’s books at $22,000, with a salvage value of $600 and a life of five years, would generate annual depreciation of $ 4,280 via the straight-line method. The SLN() function would calculate depreciation via the following logic:

Straight Line Depreciation = (Cost – Salvage) / Life = $ 21,400 / 5 yr. = $4,280 per year

Syntax

Syntactically, the parameters / arguments you provide are specified within the parentheses to the right of SLN() as shown:

SLN(<cost>, <salvage>, <life>)

The parameters are explained in the section named Shared Parameters for DAX Financial Functions for Depreciation above.

Return Value and Further Remarks

SLN() returns the straight-line depreciation for one period. Periodicity assumed within (and built into) the calculation, therefore, determines the periodicity of the output.

You’ll get some hands-on practice with SLN() in Power BI Desktop in the next section.

Practice

The operation of SLN() will become clear using the data contained in the Power BI model you have downloaded and prepared above. You’ll begin with the dataset that appears in the model, and create a calculation that employs SLN(), whose parameters are selected from the assets data in the table provided. Along with the other functions you examine in this article, the “answer” to be expected via the calculation you create will appear in the associated practice step of this section for easy comparison.

Employ the DAX SLN() Function to Generate Basic Periodic Depreciation Value

You can take the following steps to create a calculation to return basic periodic depreciation values within the sample dataset.

1. From the Report view, right-click the Assets table in the Fields pane of the model.
2. Select New column from the context menu that appears, as depicted.

Illustration 11: Creating a New Calculation …

3. Type, or cut and paste, the following into the Formula bar:

z_SLN = 
SLN(
   Assets[Cost], 
      Assets[Salvage], 
   Assets[Life]
)

The calculation appears as shown in the Formula bar:

Illustration 12: Calculation Containing the SLN() Function …

4. Click the checkmark to the left of the Formula bar to check and commit the calculation, and to create the new calculated column.

The calculation z_SLN appears within the Assets table in the Fields pane.

NOTE: You will name the calculations you create in this article with a z_ prefix. This leaves their names very close to that of the DAX financial function they employ, while making them easily identifiable (via a separate physical grouping) from the base model columns. Other methods of grouping calculations are, of course, available.

5. With the new calculated column z_SLN selected in the Fields pane, make the following Format settings underneath the main menu:
   • Currency ($)
   • 2 decimal places
   • Don’t summarize

Illustration 13: Calculated Column Format Settings

6. Select the Asset Details: Straight-Line Depreciation (z_SLN) table visualization, once again, and then click the checkbox to the left of the new z_SLN calculation. To add it to the Values section of the Fields tab for the table, underneath the existing Life column.

The z_SLN value within the Loan Details table visualization appears as shown. This can serve as a means of checking the output accuracy of the new calculation within your own Power BI Desktop model.

Illustration 14: Straight-line Depreciation Value Returned via the New Calculation

If you’ve obtained similar results to the above, you can conclude that you’ve successfully assembled a calculation to demonstrate the operation of the DAX SLN() financial function.

Straight-line depreciation makes sense to even non-accountants, assuming the concept of depreciation itself, etc., does not present an obstacle. The remaining modes of depreciation, three functions for which we will consider in the following sections, manipulate (for accounting and tax, as well as other reasons) the depreciation charged per period. This manipulation typically is driven by a need / option to accelerate depreciation or influence the rate at which the depreciation is charged – often resulting from tax considerations. A light overview of each method will be included in the discussion of each, but abundant information is available online, at the IRS and state taxing authority sites, etc., if this is of interest.

NOTE: As a tip, you may find it convenient to move the other three practice calculation tables to the left of the “table in play,” while working with any given depreciation calculation in this article – something like the arrangement shown below. You can always move things around to match what you see in the working illustrations at any given point in the practice steps.

Illustration 15: Suggested Arrangement of Working vs. Non-Working Tables in Sample Practice Exercise

Keep in mind, as you lay out your tables, that all except the first table (housing SLN() function) will have multiple additional columns, as the respective period calculation will generate a separate value, hence column, for each of the five years presented in the corresponding table visualization.

In the next section, you’ll be introduced to the DAX SYD() depreciation function.

DAX Financial Function: SYD()

According to the Data Analysis Expressions (DAX) Reference, the SYD() function “returns the sum-of-years’ digits depreciation of an asset for a specified period.” SYD() is a popular accelerated depreciation function, providing support for reducing the calculated value of an asset by a larger amount during the first period of its lifetime, and successively smaller amounts during subsequent periods.

Sum-of-years’ Digits Method: The Concepts

The sum-of-years’ digits depreciation technique accelerates depreciation based upon the assumption that assets are generally more productive when they are new, and that their productivity (and hence economic value) decreases as they become old. An example will likely help illustrate the mechanical steps behind the technique:

Example: The truck mentioned earlier, put on a company’s books at $22,000, with a salvage value of $600 and a life of five years, would have generated annual depreciation of $ 4,280 via the straight-line method.

The SYD() function would calculate depreciation via the following logic:

1. Determine the years’ digits value: Since the asset has a useful life of 5 years, the years’ digits are: 5, 4, 3, 2, and 1. The sum of the digits is 5+4+3+2+1=15.

NOTE: The sum of the digits can also be determined by using the formula (n2+n)/2 where n is equal to the useful life of the asset in years. The example would be shown as (52+5)/2=15

2. Depreciable base = Cost − Salvage value
3. SYD depreciation = Depreciable base x (Remaining useful life / Sum of the years’ digits)
4. Calculate depreciation rates for each period of life:

1. • 5/15 for the 1st year
   • 4/15 for the 2nd year
   • 3/15 for the 3rd year
   • 2/15 for the 4th year
   • 1/15 for the 5th year

Depreciation expense by respective period (year) would be generated as follows:

Period (Year)	Depreciable Base	Depreciation Rate	Depreciation Expense
1	$ 21,400	5/15	$ 7,133.33
2	$ 21,400	4/15	$ 5,706.67
3	$ 21,400	3/15	$ 4,280.00
4	$ 21,400	2/15	$ 2,853.33
5	$ 21,400	1/15	$ 1,426.67
Total			$21,400.00 ( $ 600 scrap value remains)

Table 1: Depreciation by Period via the Sum-of-years’ Digits Method

The point here is to illustrate what goes on behind the scenes when you use the DAX SYD() function. Understanding the mechanics can make it easier to intelligently select and use the function as required in the business environment, particularly when you identify the method from an examination of existing depreciation reports, built within, say, MS Excel, where the function is very similar.

Syntax

Syntactically, the parameters / arguments you provide are specified within the parentheses to the right of SYD() as shown:

SYD(<cost>, <salvage>, <life>, <per>)

The arguments common to the DAX depreciation functions as a group are explained in the section named Shared Parameters for DAX Financial Functions for Depreciation above. The Per parameter, relevant to this function, does not occur in all DAX depreciation functions.

Per – The period for which you wish to calculate depreciation. Must use the same units as Life, with a value between 1 and Life (inclusive).

Return Value and Further Remarks

The DAX SYD() function returns the Sum-of-years’ digits depreciation for the specified period. Periodicity assumed in the calculation therefore determines periodicity of the output.

You’ll get some hands-on practice with SYD() in Power BI Desktop in the next section.

Practice

The operation of SYD() will become clear using the data contained in the Power BI model you have downloaded and prepared above. You’ll begin with the dataset that appears in the model, once again, and create calculations that employ SYD(), whose parameters are selected from the assets data in the table provided. Along with the other functions you examine in this article, the “answer” to be expected via the calculations you create will appear in the associated practice step of this section for easy comparison.

Employ the DAX SYD() Function to Generate Sum-of-years’ Digits Depreciation Values

You can take the following steps to create a calculation to generate Sum-of-years’ Digits depreciation values for each of the years of the lives of the assets in the practice data set.

Create Five Separate SYD() Calculations, One for Each Year of Asset Life

1. From the Report view, right-click the Assets table in the Fields pane of the model.
2. Selecting New column from the context menu that appears, as you did within the earlier calculation, and following the steps you took in creating a calculation there, create the following five calculations within the Assets table of the model:

Calculation Name	Calculation Syntax
z_SYD-2017	z_SYD-2017 = SYD( Assets[Cost], Assets[Salvage], Assets[Life],1 )
z_SYD-2018	z_SYD-2018 = SYD( Assets[Cost], Assets[Salvage], Assets[Life],2 )
z_SYD-2019	z_SYD-2019 = SYD( Assets[Cost], Assets[Salvage], Assets[Life],3 )
z_SYD-2020	z_SYD-2020 = SYD( Assets[Cost], Assets[Salvage], Assets[Life],4 )
z_SYD-2021	z_SYD-2021 = SYD( Assets[Cost], Assets[Salvage], Assets[Life],5 )

Table 2: Sum-of-years’ Digits Method Calculations to Add to the Assets Table

The calculations appear in the Fields pane, Assets table, as depicted.

Illustration 16: z_SYD Calculations in the Fields Pane

NOTE: Approaches vary, of course, for grouping calculations and, in the business environment, placing them in a folder, etc., might have organizational advantages. For purposes of this set of practice exercises, however, you’ll keep them in simple groups via the z_ prefix as shown.

3. For each new calculated column in the z_SYD group, select the calculation and make the following Format settings (Column tools menu):
   • Currency ($)
   • 2 decimal places
   • Don’t summarize
   •  

Illustration 17: Example z_SYD Member Calculated Column Format Settings (Column Tools)

4. Ensuring that the Asset Details: Sum-of-years Digits Depreciation (z_SYD) table visualization is selected, add the new z_SYD calculations to the Values section of the Fields tab for the table, underneath the existing Life column.

The five, newly added z_SYD values, each generating the depreciation charged to the respective period year, appear within the Asset Details: Sum-of-years Digits Depreciation (z_SYD) table visualization as shown. This can serve as a means of checking the output accuracy of the new calculation within your own Power BI Desktop model.

Illustration 18: SYD Depreciation Value for Respective Year Returned via the New Calculations

Once again, if you’ve obtained similar results to the above, you can conclude that you’ve successfully assembled the calculations to demonstrate the operation of the DAX SYD() financial function over the lives of assets with the characteristics described in the data.

In the next section, you’ll be introduced to the DAX DB() depreciation function.

DAX Financial Function: DB()

According to the Data Analysis Expressions (DAX) Reference, the DB() function “returns the depreciation of an asset for a specified period using the fixed-declining balance method.” DB() is another popular accelerated depreciation function, providing support, once again, for reducing the calculated value of an asset by a larger amount during the first period of its lifetime, and smaller amounts during subsequent periods.

Fixed Declining Balance Method: The Concepts

The Fixed Declining Balance depreciation technique, like the Sum-of-years Digits (SYD() ) function I introduced in the section immediately previous, assumes that assets are generally more productive when they are new and their productivity / economic value decreases as they become old. An example, once again, may help illustrate the mechanics for the Fixed Declining Balance technique:

Example: The truck mentioned earlier, put on a company’s books at $22,000, with a salvage value of $600 and a life of five years, would have generated annual depreciation of $ 4,280 via the straight-line method.

The DB() function would calculate depreciation via the following logic:

1. Depreciable Base = Cost – Accumulated Depreciation Deduct the Depreciation Expense taken to date, from the beginning cost of the asset (in this case, do not deduct Salvage Value, which is taken into consideration within the Depreciation Rate).
2. Depreciation Rate = 1− ((Salvage / Cost) ^{(1 / Life)} (Rounded to three decimal places within the DB() function, and fairly common within accounting and finance references). This rate remains the same for each year of asset life within the Fixed Declining Balance depreciation technique, with special case consideration for first and last periods where the first period is not a full twelve months. For more information, see the Data Analysis Expressions (DAX) Reference for the DB() function.

Depreciation expense for the cited example, by respective period (year), would be generated as follows:

Period (Year)	Depreciable Base	Depreciation Rate	Depreciation Expense
1	$ 22,000.00	0.513	$ 11,286.00
2	$ 10,714.00	0.513	$ 5,496.28
3	$ 5,217.72	0.513	$ 2,676.69
4	$ 2,541.03	0.513	$ 1,305.55
5	$ 1,237.48	0.513	$ 634.83
Total			$21,400.00 ( $ 600 scrap value remains, plus small rounding difference)

Table 3: Depreciation by Period via the Fixed Declining Balance Method

Once again, the idea is to illustrate what transpires within the DAX DB() function. In most cases, the business environment will stipulate, via accounting / finance / tax considerations the depreciation policies – and hence the specific method that will drive the DAX function you select for a given task. However, it certainly helps to understand what is going on “under the covers” in cases when, say, a Power BI visualization is returning unexpected results.

Syntax

Syntactically, the parameters / arguments you provide are specified within the parentheses to the right of DB() as shown:

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

As you’ve already discovered, the arguments common to the DAX depreciation functions as a group are explained in the section named Shared Parameters for DAX Financial Functions for Depreciation above. The Period and Month (where applicable) parameters, relevant to this function, do not occur in all DAX depreciation functions.

Period – The period for which you wish to calculate depreciation. It must use the same units as Life, with a value between 1 and Life (inclusive).

Month – (Optional) The number of months in the first year. If the month is omitted, it is assumed to be 12.

Return Value and Further Remarks

The DAX DB() function returns the Fixed Declining Balance depreciation for the specified period.

You’ll get some hands-on practice with DB() in Power BI Desktop in the next section.

Practice

As with the earlier depreciation functions in the previous exercises, operation of DB() will become clear using the data contained in the Power BI model you have downloaded and prepared above. You’ll begin with the dataset that appears in the model, once again, and create calculations that employ DB(), whose parameters are selected from the assets data in the table provided. Along with the other functions you examine in this article, the “answer” to be expected via each calculation you create will appear in the associated practice step of this section for easy comparison.

Employ the DAX DB() Function to Generate Fixed Declining Balance Depreciation Values

You can take the following steps to create a calculation to generate Fixed Declining Balance depreciation values for each of the years of the lives of the assets in the practice data set.

Create Five Separate DB() Calculations, One for Each Year of Asset Life

1. From the Report view, right-click the Assets table in the Fields pane of the model.
2. Selecting New column from the context menu that appears, as you did within the calculations created for earlier practice exercise steps of this article, and following the steps you took in creating a calculation there, create the following five calculations within the Assets table of the model:

Calculation Name	Calculation Syntax
z_DB-2017	z_DB-2017 = DB( Assets[Cost], Assets[Salvage], Assets[Life],1 )
z_DB-2018	z_DB-2018 = DB( Assets[Cost], Assets[Salvage], Assets[Life],2 )
z_ DB-2019	z_DB-2019 = DB( Assets[Cost], Assets[Salvage], Assets[Life],3 )
z_ DB-2020	z_DB-2020 = DB( Assets[Cost], Assets[Salvage], Assets[Life],4 )
z_ DB-2021	z_DB-2021 = DB( Assets[Cost], Assets[Salvage], Assets[Life],5 )

Table 4: Fixed Declining Balance Method Calculations to Add to the Assets Table

The calculations appear in the Fields pane, Assets table, as depicted.

Illustration 19: z_DB Calculations in the Fields Pane

3. For each new calculated column in the z_DB group, select the calculation and make the following Format settings (Column tools menu), as done with calculations in previous sections.
   • Currency ($)
   • 2 decimal places
   • Don’t summarize
4. Ensuring that the Asset Details: Fixed Declining Balance Depreciation (z_DB) table visualization is selected, add the new z_DB calculations to the Values section of the Fields tab for the table, underneath the existing Life column.

The five, newly added z_DB values, each generating the depreciation charged to the respective period year, appear within the Asset Details: Fixed Declining Balance Depreciation (z_DB) table visualization as shown. This can serve as a means of checking the output accuracy of the new calculation within your own Power BI Desktop model.

Illustration 20: DB() Depreciation Value for Respective Year Returned via the New Calculations

If you’ve obtained similar results to the above, you can conclude that you’ve successfully assembled the calculations to demonstrate the operation of the DAX DB() financial function over the lives of assets with the characteristics described in the data.

In the next section, you’ll be introduced to the DAX DDB() depreciation function.

DAX Financial Function: DDB()

According to the Data Analysis Expressions (DAX) Reference, the DDB() function “returns the depreciation of an asset for a specified period using the double-declining balance method or some other method you specify.” DDB() is yet another depreciation function that provides support for reducing the calculated value of an asset is by a larger amount during the first period of its lifetime, and smaller amounts during subsequent periods.

Double Declining Balance Method: The Concepts

The Double Declining Balance depreciation technique shares a basic concept with other accelerated depreciation techniques – like the Sum-of-years Digits (SYD()) and the Fixed Declining Balance (DB()) financial functions I introduced in the earlier sections: The Double Declining Balance technique assumes higher productivity in newer assets, and waning productivity / economic value as assets age.

The Double Declining Balance technique, in the simplest scenario, doubles the Straight-line rate and multiples it times the book value (Cost – Accumulated Depreciation) at the beginning of the respective period.

There are a couple of twists that you might not expect with the Double Declining Balance depreciation technique – factors that reflect the traditional technique as consistently practiced within the accounting / finance context and which are reflected within the DAX DDB() financial function:

• The first thing that has confused some early adopters is a matter of the naming of the method: They have assumed, from the outset that “Double Declining Balance” simply means “two times the period depreciation generated by the Fixed Declining Balance method. This assumption reveals itself to be faulty early in the attempt to apply it in an understanding of the Double Declining Balance technique, but it might save some time to learn about the actual workings of the technique before getting started with a partial understanding.
• The Double Declining Balance calculation does not consider the salvage value in the depreciation of each period. Where book value falls below the salvage value, the last period would likely be adjusted so that it ends at the salvage value. This might need to be taken into consideration if you are, say, constructing a visualization / report involving the DDB() function, and are expecting the total of the depreciation expenses over the periods of the life of the asset to net out to precisely the economic value (cost-less-salvage value) at the end of the asset life. While a simple plug could be constructed in Power BI / other reporting mechanisms, it would be best to consult accounting / finance on the best way to handle this in your own environment. (A variable declining balance approach might be an alternative option.)

An example may help illustrate the mechanics for the Double Declining Balance technique:

Example: The truck mentioned earlier, put on a company’s books at $22,000, with a salvage value of $600 and a life of five years, would have generated annual depreciation of $ 4,280 via the straight-line method.

The DDB() function would calculate depreciation via the following logic:

1. Straight-Line Depreciation Percent = 100 % / Economic Life Simply generate an annual depreciation percent.
2. Depreciation Rate = 2 x Straight-Line Depreciation Percent The “double” in “Double-Declining Balance”
3. Depreciation for a Period = Depreciation Rate x Depreciable Base at Beginning of the Period Depreciation expense for the cited example, by respective period (year), would be generated as follows:

Period (Year)	Depreciable Base	Depreciation Rate	Depreciation Expense
1	$ 22,000.00	.400	$ 8,800.00
2	$ 13,200.00	.400	$ 5,280.00
3	$ 7,920.00	.400	$ 3,168.00
4	$ 4,752.00	.400	$ 1,900.80
5	$ 2,851.20	.400	$ 1,140.48
Total			$ 20,289.28 ( $ 600 scrap value remains, plus $ 1,110.72 difference – to be handled via accounting, etc., adjustment, as discussed above)

Table 5: Depreciation by Period via the Double Declining Balance Method

As before, the idea is to illustrate what transpires within the DAX DDB() function. It is important to always keep in mind that the business environment will stipulate, via accounting / finance / tax considerations, the depreciation policies – and hence the specific method – that will drive the DAX function you select for a given task, as I have stated already.

Syntax

Syntactically, the parameters / arguments you provide are specified within the parentheses to the right of DDB() as shown:

DDB(<cost>, <salvage>, <life>, <period>[, <factor>])

The arguments common to the DAX depreciation functions as a group are explained in the section named Shared Parameters for DAX Financial Functions for Depreciation above. The Period and Factor (where applicable) parameters, relevant to this function, do not occur in all DAX depreciation functions.

Period – The period for which you wish to calculate depreciation. Must use the same units as Life, with a value between 1 and Life (inclusive).

Factor – (Optional) The rate at which the balance declines. If factor is omitted, it is assumed to be 2 (the Double Declining Balance method).

Return Value and Further Remarks

The DAX DDB() function returns the Double Declining Balance depreciation for the specified period.

You’ll get some hands-on practice with DDB() in Power BI Desktop in the next section.

Practice

As has been the case with the depreciation functions in the previous exercises, using the data contained in the Power BI model you have downloaded and prepared above will help to clarify and activate what you’ve learned about the operation of DDB() to this point. You’ll begin with the dataset that appears in the model, once again, and create calculations that employ DDB(), whose parameters are selected from the assets data in the table provided. And, as you have done with the other functions you’ve examined in this article, the “answer” to be expected via each calculation you create will appear in the associated practice step of this section for easy comparison.

Employ the DAX DDB() Function to Generate Double Declining Balance Depreciation Values

You can take the following steps to create a calculation to generate Double Declining Balance depreciation values for each of the years of the lives of the assets in the practice data set.

Create Five Separate DDB() Calculations, One for Each Year of Asset Life

1. From the Report view, right-click the Assets table in the Fields pane of the model.
2. Selecting New column from the context menu that appears, as you did within the calculations created for earlier practice exercise steps of this article, and following the steps you took in creating a calculation there, create the following five calculations within the Assets table of the model:

Calculation Name	Calculation Syntax
z_DDB-2017	z_DDB-2017 = DDB( Assets[Cost], Assets[Salvage], Assets[Life],1 )
z_DDB-2018	z_DDB-2018 = DDB( Assets[Cost], Assets[Salvage], Assets[Life],2 )
z_DDB-2019	z_DDB-2019 = DDB( Assets[Cost], Assets[Salvage], Assets[Life],3 )
z_DDB-2020	z_DDB-2020 = DDB( Assets[Cost], Assets[Salvage], Assets[Life],4 )
z_DDB-2021	z_DDB-2021 = DDB( Assets[Cost], Assets[Salvage], Assets[Life],5 )

Table 5: Double Declining Balance Method Calculations to Add to the Assets Table

The calculations appear in the Fields pane, Assets table, as depicted.

Illustration 21: z_DDB() Calculations in the Fields Pane

4. For each new calculated column in the z_DDB() group, select the calculation and make the following Format settings (Column tools menu), as done with calculations in previous sections.
   • Currency ($)
   • 2 decimal places
   • Don’t summarize
5. Ensuring that the Asset Details: Double Declining Balance Depreciation (z_DDB()) table visualization is selected, add the new z_DDB() calculations to the Values section of the Fields tab for the table, underneath the existing Life column.

The five, newly added z_DDB() values, each generating the depreciation charged to the respective period year, appear within the Asset Details: Double Declining Balance Depreciation (z_DDB()) table visualization as shown. This can serve as a means of checking the output accuracy of the new calculation within your own Power BI Desktop model.

Illustration 22: DDB() Depreciation Value for Respective Year Returned via the New Calculations

As with the previous depreciation functions within this article, if you’ve obtained similar results to the above for DDB(), you can conclude that you’ve successfully assembled the calculations to demonstrate the operation of this financial function over the lives of assets with the characteristics described in the data.

Final Arrangement for Comparison of the DAX Depreciation Functions

Now that you’ve finished creating a set of similar tables, within a single Power BI model, that generate depreciation with each DAX depreciation function respectively, you might want to arrange these tables for easy comparison of the outputs of these functions. While you may have a specific approach in mind for designing your own arrangement, you will find that this can easily be done by taking steps similar to those that follow.

Arrange the Tables Housing the Individual DAX Depreciation Functions to Permit Easy Comparison

You can adjust sizing on the four tables you have created within the practice exercises, and then align / stack them atop each other to achieve an arrangement somewhat as shown.

Illustration 23: A Suggested Re-arrangement of the DAX Depreciation Tables

You might prefer to show only one table, based upon the exact choices of depreciation method made in the business environment, of course, or perhaps based upon parameterization to support the examination of the output of different method selections, or other variable values, at runtime. These and other options are, of course, easily accommodated with Power BI.

Summary

In this, second of a group of articles overviewing the new DAX financial functions, I introduced another popular subgroup of those functions that focuses upon depreciation. My objective was to examine how each function can be employed, within Power BI, to support analysis and reporting of the sort one might accomplish using Microsoft Excel. For each function you explored, you learned its purpose, then examined the DAX syntax involved in its use. Moreover, you gained exposure, via an illustrative example for each function, used the respective function with practice assets data, and then confirmed your understanding of the results you had obtained with each function.

 

The post DAX financial functions: Depreciation calculations appeared first on Simple Talk.

from Simple Talk https://ift.tt/3hEZbdw
via