Advanced reporting on Sales Receivables in NAV with DAX

In December 2013 we wrote about the new business area in Power Pivot that we cover for Dynamics NAV customers: sales & receivables.

Since reporting on receivables requires some steps in Power Pivot and DAX that are not very intuitive and obvious, we would like to explain the Power Pivot part in more details.  Please read this post for a detailed description of the scenario. In short we want to use Excel’s Pivot-Tables to win insights into customer’s payment behaviour –for example how many days does it on average actually take until certain customer groups or customers pay?

The NAV-part is actually quite easy since we mainly use one Query-Object that combines (joins) the Customer Ledger Entries and the Detailed Customer Ledger Entries.

Furthermore two lookup-tables that are defined as NAV-Pages (Customer and Date) are used. The tables look like the following in the diagram view:

The date table has been imported twice since dates play two different roles in this type of analysis: we want to define the point in time (Reporting Time) to report on. Additionally in order to calculate outstanding amounts, we need to work with due dates. This second due table will be hidden from the user since it is only used internally by measures. The reporting date table will be shown to the user.

The first measure that is our base measure is [Amount Receivables]:=

CALCULATE (
SUM ( CustomerLedgerEntries[Amount_LCY] );
FILTER (
ALL ( 'Dim Reporting Date' );'
Dim Reporting Date'[Date ID]
<= MAX ( 'Dim Reporting Date'[Date ID] )
)
)

The idea here is to summarize all customers ledger entries (remember that the customer ledger entries query actually is based on detailed ledger entries with some information added from the customer ledger entries) that have a document date prior to the maximum of the Dim Reporting Date table.

MAX(ColumnName) used on a date column returns the last date of the currently selected period. If we were looking for the receivables of the entire year 2014, it would return December 31st 2014; when reporting on the month level, the last date of that month would be returned – you get it.

Actually more interesting than the amount receivables is the outstanding amount.

[AmountOutstanding] :=
CALCULATE (
[Amount of receivables];
FILTER (
ALL ( 'Dim Due Date' );
'Dim Due Date'[Datum ID]
<= MAX ( 'Dim Reporting Date'[Date ID] )
)
)
We use the same base measure that we just defined but add the filter condition on the Dim Due Date tables in order to only aggregate those entries that are over-due at the reporting date.

Now let’s try to find out the “real” payment terms compared to the payment terms defined on the invoice. We first create a calculated column on the Customer Ledger Entries table:

[Days Until Closed] :=
IF (AND ( [Document_Type] = "Invoice";[Closed_at_Date] <> 0);
[Closed_at_Date] - [Document_Date])
For each customer ledger entry it calculates the number of days until it was closed (if it was closed at all).

A second very similar calculated column returns the number of days between the agreed due date and the document date of the entry what should correspond to the payment terms of that transaction:

[Days Payment Terms] :=
IF (
AND ([Document_Type] = "Invoice"; [Initial_Entry_Due_Date] <> 0);
[Initial_Entry_Due_Date] - [Document_Date]
)

Using this calculated column we create one intermediary measure  that summarizes all customer ledger entries that have been closed:

[Amount Entries completely paid] :=
CALCULATE (
[Amount Customer Ledger Entries];
CustomerLedgerEntries[Days Until Closed] <> 0)

Now we can create our main measure that calculates how many days on average it took until entries were closed. In order to take the value of different invoices into consideration, we use a weighted average based on the Amount. SUMX is of great help to calculate weighted averages. The idea is to go through each customer ledger entry and to multiply the Days Until Closed of that entry with the entry amount and to divide the sum of all multiplications by the amount of all entries:

[Avg. Days until Closed (Weighted)] :=
DIVIDE (
SUMX (
FILTER (
CustomerLedgerEntries;
CustomerLedgerEntries[Document_Type] = "Invoice"
);
[Amount Entries completely paid] * [Days Until Closed]);[Amount Entries completely paid]
)

In a very similar way we can calculate a weighted average of the agreed time for payment:

[Avg. Days until Closed Payment Terms (Weighted)] :=
DIVIDE (
SUMX (
FILTER (
CustomerLedgerEntries;
CustomerLedgerEntries[Document_Type] = "Invoice"
);
[Customer Sales] * CustomerLedgerEntries[Days Payment Terms]
);
[Customer Sales]
)

In order to compare the agreed payment terms with the reality we just need to calculate the difference between both.

[Payment Terms vs. Actual] :=
IF (
NOT (ISBLANK ( [Avg Days until Closed (Weighted)] ));
[Avg Days until Closed (Weighted)]- 
[Avg Days Payment Terms (Weighted)]
)

Adding some conditional formatting brings the view in a CRONUS Extended database with the customer name on the row level:

Most of Cronus’ customers obviously pay in time.

Instead of reporting on the customer level we can change the perspective by showing the measure Payment Terms vs. Actual by Country and city over time:

Analysing NAV’s receivables in Power Pivot has a lot of additional potential – identifying the receivables turnover rate, days sales outstanding etc. - everything based on that simple data model with some DAX-calculations.

Since setting up the NAV-part requires some help from our side, please contact us if you are interested in testing this solution, we will be happy to provide the FOBs and Excel-files. 

Update: A very attentive reader of our blog asked why we did not define the calculated columns [Days Until Closed] and [Days Payment Terms] in a much simpler way by just subtracting the values. Power Pivot is smart enough to handle that sort of calculation and actually returns the number of days what is just what we need.  We updated the measures in this post, the original measures can be found here. The same reader experienced slow performance on files with a high number of entries, what is the result of the SUMX-function in the measure [Avg. Days until Closed (Weighted)]. It needs to make the inner calculation for each customer ledger entry to calculate the weighted average. We will follow-up on the topic of optimizing the performance in DAX - possible solution here are to move a part of the calculation to calculated columns or even to the source queries.