Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Depreciation Pattern for SSAS Tabular and Power Pivot Data Models

  • 21 March 2015
  • Author: TyroneBrown
  • Number of views: 11710
  • 0 Comments

This post is to describe a pattern to measure the depreciation of an expense using DAX in a SSAS Tabular or Power Pivot data model. The pattern accounts for a variable length of depreciation, called here a Depreciation Schedule. The pattern will account for a depreciation expense for an equal amount for every year of the depreciation schedule. For example: If an expense is $10,000 and has a Depreciation Schedule of 2 years, then we could account for $5,000 the first year, and $5,000 the second year.

To begin, this example will use Power Pivot to illustrate a quick prototype of the pattern using some generated sample data. First, you need a date table. Chris Webb has a useful post on how you can generate a date table using Power Query and M here. I’ve used that pattern here to generate a date table for 01/01/2014 – 12/31/2016. Once you’ve created your table in Power Query, load it into your Power Pivot data model using the “Load To” button in your query.

After you have your date table, we’ll create one more table with some a purchase that will be depreciated. See the picture below. I’ve only created one record for a single $100,000 purchase, with a Depreciation Schedule of 5 years, that was purchased on 01/01/2015.

Depreciation Linked Table

After you’ve created your table, you’ll need add it to the Power Pivot data model. To do this, highlight the table. Then, select your Power Pivot tab, and the Add to Data Model button.

Add to Data Model

Once you’ve loaded both tables into your data model, open up the Power Pivot screen with the green Manage button to work in your data model.

The first thing we’ll do in the data model is to create a relationship between to the Purchase Date column and the Date column in the Date table. To do this, highlight the Purchase Date column in your Purchase table, then select Create Relationship. This will open the Create Relationship dialog box. Here you’ll match the Purchase Date to the Date column in the date table. See picture below:

Create Relationship

Now we’ll need to add additional relationships between the two tables. We’ll need one relationship for every year of our depreciation schedule. So it will be important to note the maximum value of all of the depreciation schedules that we need to account for. In our example, we’ll use a 5 year schedule, so we’ll add four additional calculated columns. These calculated columns will be 1 year from our purchase date, 2 years from our purchase date, and so forth. See below for the DAX expression for the calculated column.

=DATEADD ( ‘Date'[Date], 1, YEAR )

Repeat this calculation for each column while incrementing the integer by 1 for each column. The results will looks like the picture below. Notice they I’ve hidden these columns from the data model since they’ll only be used for to create our inactive relationships.

Calculated Columns

Once you have all the columns created, create relationships for each columns to the Date[Date] column. Since there is already a relationship between our two tables, any additional relationships we add here will be considered inactive relationships. That’s okay. We’ll just need to keep this in mind when we develop measures that need to use these inactive relationships. See picture below.

Create Inactive Relationships

After you create all of the inactive relationships, its time to create calculated measures. We’re going to create a separate measure for each depreciation amount that is accounted for in the Deprecation Schedule. The first calculated measure will be just the amount depreciated during the current year of the depreciation schedule. Notice that we have a possibility that an expense will be accounted for in full in the purchase year. To account for that, test for a BLANK() or a zero value for the Depreciation Schedule. If there is a Depreciation Schedule with a positive number, divide the Amount column with the Depreciation Schedule.

Depreciation Purchase Year :=
SUMX (
‘Expenses’,
IF (
[Depreciation Schedule (Yrs)] < 1
|| ISBLANK ( [Depreciation Schedule (Yrs)] ),
[Amount],
DIVIDE ( [Amount], [Depreciation Schedule (Yrs)] )
)
)

The next step will be to create a depreciation measure for every other year in the Depreciation Schedule. These measures will be similar, but slightly different. These measures only exist in the context of their respective dates, whose relationships are inactive. Since these relationships are inactive, we have to explicitly state which relationship to use when you create the measures. See code below for the first two year along the depreciation schedule:

Depreciation Year 1 From Purchase :=
CALCULATE (
SUMX (
‘Expenses’,
DIVIDE ( ‘Expenses'[Amount], [Depreciation Schedule (Yrs)] )
),
USERELATIONSHIP ( Expenses[Year 1 After Purchase], ‘Date'[Date] )

Depreciation Year 2 From Purchase :=
CALCULATE (
SUMX (
‘Expenses’,
DIVIDE ( ‘Expenses'[Amount], [Depreciation Schedule (Yrs)] )
),
USERELATIONSHIP ( Expenses[Year 2 After Purchase], ‘Date'[Date] )
)

Repeat the above code example for all years along the Depreciation Schedule, by changing first parameter of the USERELATIONSHIP() function to use the year for the respective year of the Depreciation Schedule.

After all of the yearly depreciation calculated measures are created, we can add them up so that we can have a full accounting of the depreciation in one column. To create this, simply add up all of the calculated measures in a new measure called Total Depreciation.

Total Depreciation :=
[Depreciation Purchase Year]
+ [Depreciation Year 1 From Purchase]
+ [Depreciation Year 2 From Purchase]
+ [Depreciation Year 3 From Purchase]
+ [Depreciation Year 4 From Purchase]

Now all of the calculation are created for the accounting of depreciation. The Total Depreciation measure can now be used for the gross depreciation amount for a given period. If you want to see the breakdown, you can add the yearly measures in a Pivot Table.

image

image

The next step is to create a calculated measure for the Deferred Expenses in a period. This measure will be used to create a running deferment balance. See code below.

Deferred Expenses :=
SUMX (
‘Expenses’,
IF (
[Depreciation Schedule (Yrs)] > 0
|| NOT ( ISBLANK ( [Depreciation Schedule (Yrs)] ) ),
[Amount]
)
)

Then to complete the pattern, create a running balance for all deferred expenses.

Deferred Balance :=
IF (
CALCULATE (
[Deferred Expenses],
FILTER (
ALL ( ‘Date’ ),
‘Date'[Date] <= MAX ( ‘Date'[Date] )
)
)
– CALCULATE (
[Total Depreciation],
FILTER (
ALL ( ‘Date’ ),
‘Date'[Date] <= MAX ( ‘Date'[Date] )
)
)
= 0,
BLANK (),
CALCULATE (
[Deferred Expenses],
FILTER (
ALL ( ‘Date’ ),
‘Date'[Date] <= MAX ( ‘Date'[Date] )
)
)
– CALCULATE (
[Total Depreciation],
FILTER (
ALL ( ‘Date’ ),
‘Date'[Date] <= MAX ( ‘Date'[Date] )
)
)
)

Below we can see what our depreciation pattern looks like in a pivot table.

image

Print
Categories: Big Data
Tags:
Rate this article:
No rating

TyroneBrownTyroneBrown

Other posts by TyroneBrown

Please login or register to post comments.