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])
v-dineshya
2 months agoCommunity Support
Hi rajasekaro ,
Thank you for reaching out to the Microsoft Community Forum. could you please try the proposed solution shared by Lodha_Jaydeep ? Let us know if you’re still facing the same issue we’ll be happy to assist you further.
Regards,
Dinesh
v-dineshya
2 months agoCommunity Support
Hi rajasekaro ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh