Forum Discussion
rajasekaro
2 months agoHelper III
Closing value
Hi team , how to calculate closing value i have account data ACCOUNT APYC DATE AMOUNT 43060 482727800 30-04-2024 -360868242 43060 482727800 31-05-2024 -92756178 43060 4827...
- 2 months ago
Hi rajasekaro,
Please have a look at the below solution.1) Create power Bi measures for the closing
ClosingV3 = VAR Opening = MAX(account_transactions[APYC]) VAR CurrentDate = MAX(account_transactions[DATE]) VAR CurrentAccount = MAX(account_transactions[ACCOUNT]) VAR CumulativeAmount = CALCULATE( SUM(account_transactions[AMOUNT]), REMOVEFILTERS(account_transactions), account_transactions[ACCOUNT] = CurrentAccount, account_transactions[DATE] <= CurrentDate ) RETURN Opening + CumulativeAmountOR
Closing_v4 = VAR Opening = MAX(account_transactions[APYC]) RETURN Opening + CALCULATE( SUM(account_transactions[AMOUNT]), ALLEXCEPT(account_transactions, account_transactions[ACCOUNT]), account_transactions[DATE] <= MAX(account_transactions[DATE]) )2) Copy that measure in the table/matrix visual
Please consider as an accepted solution if worked or give some kudod.
- 2 months ago
if you want to create a column ,you can try this
Column = 'Table'[APYC]+sumx(FILTER('Table','Table'[ACCOUNT]=EARLIER('Table'[ACCOUNT])&&'Table'[DATE]<=EARLIER('Table'[DATE])),'Table'[AMOUNT])
ryan_mayu
2 months agoSuper User
if you want to create a column ,you can try this
Column = 'Table'[APYC]+sumx(FILTER('Table','Table'[ACCOUNT]=EARLIER('Table'[ACCOUNT])&&'Table'[DATE]<=EARLIER('Table'[DATE])),'Table'[AMOUNT])