Forum Discussion

Chris_WS's avatar
Chris_WS
Regular Visitor
3 years ago

Aged Reporting using data from Sage 50 Accounts

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 r...
Need Help
Chris_WS's avatar
Chris_WS
Regular Visitor
3 years ago

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's avatar
BA_Pete
Super User
3 years ago

 

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's avatar
    Chris_WS
    Regular Visitor
    2 years ago

    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.