Forum Discussion
Alternative for AverageX
Hi Anonymous
could you try to rewrite the code to this:
Average Balance (Period) =
AVERAGEX (
values(account number),
[Daily Average Balance]
)
The idea is that the daily average balance has to be computed for each account number, and after that compute the average balance for all accounts. There is no need to include any time handling in the expression, it will be evaluated in the context from the report. E.g. if the report has a month slicer and April is selected, then the measure will calculate the average balance of April
Cheers,
Sturla
Though your suggestion improves the performance, but it does not yield the desired results. It does not consider the dates when there was no transaction for a particular Accound Code. Also I have multiple hierarchy levels for Account code, and report can be drawn at any level.
hm,
not sure if this will yield any better performance:
Average Balance (Period) = VAR _minDate = MIN ( 'Date'[Date] ) VAR _maxDate = MAX ( 'Date'[Date] ) RETURN AVERAGEX ( FILTER ( ALL ( 'Date' ), 'Date'[Date] >= _minDate && 'Date'[Date] <= _maxDate ), [Daily Average Balance] )If you provide some sample data it will be easier to help you
There was not a major performance improvement with your last suggestion.
Following is relevant sample dataset-
This is the Accounting table on which Average Balance (rolling) needs to be calculated on [Daily Average Balance], which is a measure based on Home_Currency_Amount, Debit_Credit, and related Date and GL Master tables. This measure works perfectly fine separately.
source_system_code
entity_code
effective_date
posted_date
SubLob_Code
GL_code
home_currency_amount
debit_credit
ORA01
181
04/01/2020
04/01/2020
183
727104
213
DR
ORA01
101
04/01/2020
04/01/2020
G41
733102
1269.82
DR
ORA01
101
04/01/2020
04/01/2020
G41
733102
1006.5
CR
ORA01
181
04/01/2020
04/01/2020
E92
734113
303
CR
ORA01
181
05/01/2020
05/01/2020
183
727102
66
DR
The Accounting table is linked to a Date Table through Effective Date and following GL Master table (linked through SourceSystemCode + GL Code):
Source_System_Code
GL_CODE_DESCRIPTION
GL Code Level 1
GL Code Level 2
GL Level 3
ACCOUNT_TYPE
IMB01
KRISHNA PRATAP SINGH
KRISHNA PRATAP SINGH
Trade payables to non-related parties
Financial Liability
Liability
ORA01
Prepayment Migration A/c
Prepayment Migration A/c
Control Accounts
Non Financial Assets
Assets
ORA01
Salaries and wages - Sharing
Salaries and wages - Sharing
Salaries and wages - Sharing
Employee Benefit Expenses
Expenses
Thanks a lot for your help!
what do the code for [(Cumulative) Closing Balance] & [(Cumulative) Opening Balance] look like?