Aged Reporting using data from Sage 50 Accounts

[Chris_WS]

Hi

I have a requirement to reproduce Sage 50 reporting in Power BI, but as I am not an accountant, or familiar with Sage 50, the Aged Debtors and Creditors reports are proving difficult. I have reproduced them for the current date, but the method for 'ageing' them seems obscure.

I imagine I need to use 2 queries, but don't really know where to start. Which fields need to be amended and where can I find the data to 'Exclude later transactions'?

Many thanks.

[Chris_WS]

This is really helpful, thanks Pete. I am also hoping to create a point in time aged report, which is causing me the most problems. It appears that there is no way to feed user input into calculated fields within Power BI. So I cannot use filters or slicers to calculate aged values based on a historical point in time (eg, reporting on Qtr2 position). I've solved it by linking to an external spreadsheet, where users can set the report date.

This is a clumsy work-around, and if there is any way to circumvent this limitation I would be eternally grateful.

[BA_Pete]

In order to do a point-in-time report, you will need a [PAID_DATE] field in order to know if a certain debt value should contribute towards the report at the given point-in-time.

If/when you have this field, your basic DAX measure structure would be something like this:

_agedDebtValue_PiT =
VAR __cDate = MAX(calendar[date])
RETURN
CALCULATE(
    SUM(yourTable[DEBT_VALUE]),
    KEEPFILTERS(__cDate >= yourTable[DUE_DATE]),
    KEEPFILTERS(__cDate < yourTable[PAID_DATE])
)

You would then use calendar[date] on either a chart axis with this measure, or allow the end user to select a date using a slicer.

Of course, this isn't going to be able to tell you how old each part of the debt was at the selected point in time, but good to be able to see the total aged value changing over time.

This also gets further complicated if your clients (?) can make part-payments against a single debt, as then you'd need all of the part transactions to be deducted from the overall debt in the same point-in-time manner, but hopefully this can give you some ideas for the future.

Pete

[Chris_WS]

It turns out that what I'm trying to do is fundamentally not possible, using data returned by the user via filters or slicers. Because the tables are point-in-time representations of the data at the point of refresh, user input can't update calculated columns, which is frustrating.

Thanks very much for the help, I'm getting around it by using a spreadsheet to hold the desired report date.

[BA_Pete]

Hi @Chris_WS ,

In the source data, do you have columns that give you Due Date and Paid Date?

Pete

[Chris_WS]

The AUDIT_HEADER table has a DUE_DATE field. It also has PAID_FLAG and PAID_STATUS, but no Paid Date. Advice I've found elsewhere suggests that you can use the AUDIT_USAGE table to identify payments after the required date for Ageing.

[BA_Pete]

Ok. I'm not familiar with the Sage50 reports that you're trying to reproduce so, if the following isn't what you're after, then you'll need to provide an example of the actual output you're trying to create.

You only need paid date if you want to do point-in-time reporting. As aged debt tends to be a snaphot metric, i.e. you only generally want to know what your position is now, then you can do this with the currently-available fields, something like this:

-1- In Power Query, create a new custom column to calculate the debt age ([debtAge]), something like the following:

if [PAID_FLAG] = 1 then null
else Number.From(Date.From(DateTime.LocalNow())) - Number.From(Date.From([DUE_DATE]))

-2- Create another custom column for grouping the debt age:

if [PAID_FLAG] = 1 then null
    else if [debtAge] <= 0 then "Not Due"
    else if [debtAge] <= 30 then "1-30"
    else if [debtAge] <= 60 then "31-60"
    else if [debtAge] <= 90 then "61-90"
    else if [debtAge] <= 180 then "91-180"
    else if [debtAge] <= 365 then "181-365"
else "> 1 year"

-3- Create a further column so you can sort these text values in your report:

if [PAID_FLAG] = 1 then 999
    else if [debtAge] <= 0 then 1
    else if [debtAge] <= 30 then 2
    else if [debtAge] <= 60 then 3
    else if [debtAge] <= 90 then 4
    else if [debtAge] <= 180 then 5
    else if [debtAge] <= 365 then 6
else 7

This should give you the basis for aged debt reporting.

Pete