Forum Discussion
Alternative for AverageX
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!