Depreciation Pattern for SSAS Tabular, Power BI, and Power Pivot Data Models

Depreciation Pattern for SSAS Tabular, Power BI, and Power Pivot Data Models

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

 
Comments

Hi TJ, and thank you for this interesting post.

I have one question, how do you manage the depreciation calculation, for instance, with a purchase in the middle of the month ?
Regards

Since the calculations are based on the date, there’s no problem with purchases that happen in the middle of the month as long as we have a date table with one record for every day. You can roll it up on the day level, week, month, or year. You’d only have to modify the calcs if you wanted to depreciate by a different period.

Thanks for the reply!

Hi TJ,
I’m rather new at Power BI and trying to wrap my head around this pattern with the added twist that I need to account for the depreciation in monthly periods, but summarize it on an annual basis considering which month during a year the depreciation started. Can you elaborate on how to tweak the scenario?
Thanks for any help.
Andy