Forum Discussion
Alternative for AverageX
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!
Resident Rockstarwhat 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
- sturlaws
hmm,
so the way the opening and closing balance is calculated, is to sum up all prior transactions. So if a report user is trying to see the average daily balance for a month, using averagex, this process is repeated for each day. And if the report is drilled down to GL Code Level 1, the number of calculations is multiplied with number of GL Code Level 1 visible in the visual. With a table with small number of rows it will work. With a table with a high number of rows, it will be slow and you might run out of memory.
I think you will have to create a table containing the opening and closing balance for each account for each day in order to imrpove the query speed.