Forum Discussion
Alternative for AverageX
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.
Resident Rockstarhm,
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!
- sturlaws
what do the code for [(Cumulative) Closing Balance] & [(Cumulative) Opening Balance] look like?
(Cumulative) Closing Balance =
CALCULATE([Closing Balance]|
filter ( ALL('Date'[Date])|'Date'[Date] <= max ('Date'[Date]))
)(Cumulative) Opening Balance =
CALCULATE([Closing Balance]|
filter ( ALL('Date'[Date])|'Date'[Date] <= (max ('Date'[Date])-1))
)where Closing Balance =
Var Amount = Sum(Accounting[Home_Currency_Amount]))
Var Debit_Amt = CALCULATE([Amount],Accounting[Debit_Credit]="DR")
Var Credit_Amt = CALCULATE([Amount],Accounting[Debit_Credit]="CR")
Return
Debit_Amt - Credit_Amt