Collections Reporting Using Power Pivot

Level: Beginners

I was helping a user at http://powerpivotforum.com.au with a question about collections reporting and thought it would make a good blog post. There are a few interesting concepts involved which I will explain as I go along.

The scenario is as follows. We have 2 tables – one table contains all invoices (the master list) and the second table contains the invoices that have been paid along with the payment date. Here is what the payment table looks like.  It’s kind of like an aged payment report.

The requirement is to produce a report like the one shown below. The column headers are months in the format YYMM and the rows of the Pivot Table show the timing of the payment relative to the month the payment is due.

In the picture above, 41.3% of all money due in Jan 2015 was collected in that month (shown as #1). 8.4% of the money due in Feb 2015 was collected 2 months late (ie April 2015 in this instance).

You will need Calculated Columns for this Scenario

Those of you that have been to one of my training courses or have read my book will know that I always warn Excel users to avoid calculate columns most of the time. But in this case you will need to use a calculated column to create the values T-1, T0, T1 etc (I refer to these as Payment Zones). The reason you need to use a calculated column and not a measure is because in this instance the Payment Zones are needed in the Rows on the pivot table – you cannot put Measures on rows (or columns, filters, slicers either for that matter).

First the data model

I created a calendar table that contains all dates in the time horizon for analysis and connected this table to the data tables on Due Date. You can read about good practice for calendar tables here. Note that I have a MonthID column in my calendar table. This is a unique Integer ID starting at 1 and advancing by 1 each month. This is an essential part of the solution as you will read below. I also have created a table called PaymentZones. This table contains all the possible values of T-1, T0, T2 etc as well as a sort column. Finally you can see the 2 tables (invoice and payment data) down the bottom.

Now the DAX Measures and Calculated Columns

There is only 1 measure in the Invoices Table. This calculates the total amount due each month.

Total All Invoices:=sum(Invoices[Invoice Amount])

In the payments table I have created 3 calculated columns as follows.

The column Due Month uses the RELATED function to fetch the unique MonthID from the calendar table.

=RELATED(Calendar[MonthID])

It is not possible to fetch the Paid Month ID using the same method as above because both of the data tables are connected on Due Date. It is essential to connect both tables on Due Date because otherwise the [% Collected] measure will not work. Instead I have used the following formula to fetch the ID for the Paid Month.

=LOOKUPVALUE(Calendar[MonthID],Calendar[Date],Payments[Payment Date])

Once I have both of these IDs in my table, I can subtract one from the other and prepend at “T” to the front to create the Payment Zone Column.

="T"&[Paid Month]-[Due Month]

Note that in DAX it is perfectly OK to do a concatenation and numeric calculations in the same formula. Quite cool actually.

Now that I have created the Payment Zone column, I connected this new column to the Payment Zone lookup table. This table contains a sort column to help with the layout of the pivot table. I used the “sort by column” button in Power Pivot to force the Payment column to sort by the “Sort” column instead.

Note: It is good practice to write your calculated columns one at a time like I have shown above – this helps you break the problem into manageable bits. However once you have everything working, it is also good practice to consolidate the interim columns into a single final column and delete the interim columns. This ensures the file stays as small as possible and will improve performance. Note that doing this also makes the DAX formula much harder to read and understand (as can be seen below), so just use your judgment on which is best for you.

Consolidated final single column

 =
 "T"
   & LOOKUPVALUE(Calendar[MonthID],Calendar[Date],Payments[Payment Date])
            - RELATED(Calendar[MonthID])

Now for the final few DAX Measures. The Total Amount collected is the same formula as Total All Invoices however it only adds up the payments actually collected in the payments table.

Finally the % collected is simply the amount collected divided by the total due.

And here is the final result.

And here is the sample file if you want to take a look. Payment Timing