Forum Discussion
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 | 482727800 | 30-06-2024 | 7020311 |
| 43060 | 482727800 | 31-07-2024 | 107432449 |
| 43060 | 482727800 | 31-08-2024 | 79516473 |
| 43060 | 482727800 | 30-09-2024 | -28624685 |
| 43060 | 482727800 | 31-10-2024 | -51472204 |
| 43060 | 482727800 | 30-11-2024 | 101618069 |
| 43060 | 482727800 | 31-12-2024 | -864144 |
| 43060 | 482727800 | 31-01-2025 | -14221816 |
| 43060 | 482727800 | 28-02-2025 | -12359813 |
| 43060 | 482727800 | 31-03-2025 | 25600331 |
Expected Out put
| ACCOUNT | APYC | DATE | AMOUNT | Closing |
| 43060 | 482727800 | 30-04-2024 | -360868242 | 121859558 |
| 43060 | 482727800 | 31-05-2024 | -92756178 | 29103380 |
| 43060 | 482727800 | 30-06-2024 | 7020311 | 36123691 |
| 43060 | 482727800 | 31-07-2024 | 107432449 | 143556140 |
| 43060 | 482727800 | 31-08-2024 | 79516473 | 223072613 |
| 43060 | 482727800 | 30-09-2024 | -28624685 | 194447928 |
| 43060 | 482727800 | 31-10-2024 | -51472204 | 142975724 |
| 43060 | 482727800 | 30-11-2024 | 101618069 | 244593793 |
| 43060 | 482727800 | 31-12-2024 | -864144 | 243729649 |
| 43060 | 482727800 | 31-01-2025 | -14221816 | 229507833 |
| 43060 | 482727800 | 28-02-2025 | -12359813 | 217148020 |
| 43060 | 482727800 | 31-03-2025 | 25600331 | 242748351 |
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.
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])
7 Replies
Hello rajasekaro
try this
Closing =
VAR CurrentDate = MAX(account_transactions[DATE])
VAR CurrentAccount = MAX(account_transactions[ACCOUNT])
VAR Opening = MAX(account_transactions[APYC])RETURN
Opening +
CALCULATE(
SUM(account_transactions[AMOUNT]),
REMOVEFILTERS(account_transactions),
account_transactions[ACCOUNT] = CurrentAccount,
account_transactions[DATE] <= CurrentDate
)
If my response helped you, please consider clicking
Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.
Thanks,
Connect with me on:
LinkedIn |
Data With Pankaj - YouTubeHi 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.
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
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
Hello,
I think like thisClosing =
VAR CurrentDate = 'Table'[DATE]
VAR OpeningBalance = 'Table'[APYC]
VAR RunningAmount =
CALCULATE(
SUM('Table'[AMOUNT]),
FILTER(
ALLEXCEPT('Table', 'Table'[ACCOUNT], 'Table'[APYC]),
'Table'[DATE] <= CurrentDate
)
)
RETURN
OpeningBalance + RunningAmountkind regards,
Danieleif 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])rajasekaro sorry to add another solution here, everyone else has done great work but I disagree with above solutions. Anything related to time intelligence should be handles using calendar/date table (as a best practice). Learn more about that, check here:
As a best practice, add a date dimension in your model and use it for time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools. Check the related videos on my YT channel
Add Date Dimension
Importance of Date Dimension
Mark date dimension as a date table - why and how?
Time Intelligence Playlist
I will create a date dimension and use this to work with dates, and setup relationship of this table with the transaction/fact table on the date column.Sum Amount = SUM ( Table2[AMOUNT] ) --just a base measure RT Amount = CALCULATE ( [Sum Amount], FILTER ( ALLSELECTED ( 'Calendar' ), --change this will all if want to ignore any filter on date 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) ) ) Closing Value = MAX ( Table2[APYC] ) + [RT Amount]
Cheers!
