Forum Discussion
Aged Reporting using data from Sage 50 Accounts
Super User
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
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.