Forum Discussion
Acumulate Formula with conditions
Hi everyone, I have a question about a Calculate Measure.
In the next image, I have an example of the behavior that must had my Initial Balance and the Final Balance.
I must say, that I have a Fact table with the movements, and this fact table is connected to the dimensión table (Account dimension) by the account number. The important field is also "TC" (this is the kind of account).
The normal behavior of the Initial Balance is to accumulate the past years movements, but There is an Exception when the TC (kind of account) is equal to “C”, The Initial Balance for January is always Zero (because never accumulate past years) and This “TC” only acumulate the movements of the selected year.
This is the movements in the Fact Table, and I also have a dimension table for Accounts and their "TC".
This is an example of the filters in the dashboard and 4 different selections of months
Does anyone know, how to do the DAX formula to calculate the Initial Balance and the final balance?
6 Replies
- lbendlinSuper User
Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523- MiguelSaldana77Resolver I
AccountTCKind_MovementYearMonthAmountDateChargers_January 100 A Charge 2020 1 20 01/01/2020 12:00:00 a. m. 100 A Charge 2020 2 20 01/02/2020 12:00:00 a. m. 100 A Charge 2020 3 20 01/03/2020 12:00:00 a. m. 100 A Charge 2020 4 20 01/04/2020 12:00:00 a. m. 100 A Charge 2020 5 20 01/05/2020 12:00:00 a. m. 100 A Charge 2020 6 20 01/06/2020 12:00:00 a. m. 100 A Charge 2020 7 20 01/07/2020 12:00:00 a. m. 100 A Charge 2020 8 20 01/08/2020 12:00:00 a. m. 100 A Charge 2020 9 20 01/09/2020 12:00:00 a. m. 100 A Charge 2020 10 20 01/10/2020 12:00:00 a. m. 100 A Charge 2020 11 20 01/11/2020 12:00:00 a. m. 100 A Charge 2020 12 20 01/12/2020 12:00:00 a. m. 100 A Payment 2020 1 -10 01/01/2020 12:00:00 a. m. 100 A Payment 2020 2 -10 01/02/2020 12:00:00 a. m. 100 A Payment 2020 3 -10 01/03/2020 12:00:00 a. m. 100 A Payment 2020 4 -10 01/04/2020 12:00:00 a. m. 100 A Payment 2020 5 -10 01/05/2020 12:00:00 a. m. 100 A Payment 2020 6 -10 01/06/2020 12:00:00 a. m. 100 A Payment 2020 7 -10 01/07/2020 12:00:00 a. m. 100 A Payment 2020 8 -10 01/08/2020 12:00:00 a. m. 100 A Payment 2020 9 -10 01/09/2020 12:00:00 a. m. 100 A Payment 2020 10 -10 01/10/2020 12:00:00 a. m. 100 A Payment 2020 11 -10 01/11/2020 12:00:00 a. m. 100 A Payment 2020 12 -10 01/12/2020 12:00:00 a. m. 100 A Charge 2021 1 20 01/01/2021 12:00:00 a. m. 100 A Charge 2021 2 20 01/02/2021 12:00:00 a. m. 100 A Charge 2021 3 20 01/03/2021 12:00:00 a. m. 100 A Charge 2021 4 20 01/04/2021 12:00:00 a. m. 100 A Charge 2021 5 20 01/05/2021 12:00:00 a. m. 100 A Charge 2021 6 20 01/06/2021 12:00:00 a. m. 100 A Charge 2021 7 20 01/07/2021 12:00:00 a. m. 100 A Charge 2021 8 20 01/08/2021 12:00:00 a. m. 100 A Charge 2021 9 20 01/09/2021 12:00:00 a. m. 100 A Charge 2021 10 20 01/10/2021 12:00:00 a. m. 100 A Charge 2021 11 20 01/11/2021 12:00:00 a. m. 100 A Charge 2021 12 20 01/12/2021 12:00:00 a. m. 100 A Payment 2021 1 -10 01/01/2021 12:00:00 a. m. 100 A Payment 2021 2 -10 01/02/2021 12:00:00 a. m. 100 A Payment 2021 3 -10 01/03/2021 12:00:00 a. m. 100 A Payment 2021 4 -10 01/04/2021 12:00:00 a. m. 100 A Payment 2021 5 -10 01/05/2021 12:00:00 a. m. 100 A Payment 2021 6 -10 01/06/2021 12:00:00 a. m. 100 A Payment 2021 7 -10 01/07/2021 12:00:00 a. m. 100 A Payment 2021 8 -10 01/08/2021 12:00:00 a. m. 100 A Payment 2021 9 -10 01/09/2021 12:00:00 a. m. 100 A Payment 2021 10 -10 01/10/2021 12:00:00 a. m. 100 A Payment 2021 11 -10 01/11/2021 12:00:00 a. m. 100 A Payment 2021 12 -10 01/12/2021 12:00:00 a. m. 400 C Charge 2020 1 20 01/01/2020 12:00:00 a. m. 400 C Charge 2020 2 20 01/02/2020 12:00:00 a. m. 400 C Charge 2020 3 20 01/03/2020 12:00:00 a. m. 400 C Charge 2020 4 20 01/04/2020 12:00:00 a. m. 400 C Charge 2020 5 20 01/05/2020 12:00:00 a. m. 400 C Charge 2020 6 20 01/06/2020 12:00:00 a. m. 400 C Charge 2020 7 20 01/07/2020 12:00:00 a. m. 400 C Charge 2020 8 20 01/08/2020 12:00:00 a. m. 400 C Charge 2020 9 20 01/09/2020 12:00:00 a. m. 400 C Charge 2020 10 20 01/10/2020 12:00:00 a. m. 400 C Charge 2020 11 20 01/11/2020 12:00:00 a. m. 400 C Charge 2020 12 20 01/12/2020 12:00:00 a. m. 400 C Payment 2020 1 -10 01/01/2020 12:00:00 a. m. 400 C Payment 2020 2 -10 01/02/2020 12:00:00 a. m. 400 C Payment 2020 3 -10 01/03/2020 12:00:00 a. m. 400 C Payment 2020 4 -10 01/04/2020 12:00:00 a. m. 400 C Payment 2020 5 -10 01/05/2020 12:00:00 a. m. 400 C Payment 2020 6 -10 01/06/2020 12:00:00 a. m. 400 C Payment 2020 7 -10 01/07/2020 12:00:00 a. m. 400 C Payment 2020 8 -10 01/08/2020 12:00:00 a. m. 400 C Payment 2020 9 -10 01/09/2020 12:00:00 a. m. 400 C Payment 2020 10 -10 01/10/2020 12:00:00 a. m. 400 C Payment 2020 11 -10 01/11/2020 12:00:00 a. m. 400 C Payment 2020 12 -10 01/12/2020 12:00:00 a. m. 400 C Charge 2021 1 40 01/01/2021 12:00:00 a. m. 400 C Charge 2021 2 20 01/02/2021 12:00:00 a. m. 400 C Charge 2021 3 20 01/03/2021 12:00:00 a. m. 400 C Charge 2021 4 20 01/04/2021 12:00:00 a. m. 400 C Charge 2021 5 20 01/05/2021 12:00:00 a. m. 400 C Charge 2021 6 20 01/06/2021 12:00:00 a. m. 400 C Charge 2021 7 20 01/07/2021 12:00:00 a. m. 400 C Charge 2021 8 20 01/08/2021 12:00:00 a. m. 400 C Charge 2021 9 20 01/09/2021 12:00:00 a. m. 400 C Charge 2021 10 20 01/10/2021 12:00:00 a. m. 400 C Charge 2021 11 20 01/11/2021 12:00:00 a. m. 400 C Charge 2021 12 20 01/12/2021 12:00:00 a. m. 400 C Payment 2021 1 -10 01/01/2021 12:00:00 a. m. 400 C Payment 2021 2 -10 01/02/2021 12:00:00 a. m. 400 C Payment 2021 3 -10 01/03/2021 12:00:00 a. m. 400 C Payment 2021 4 -10 01/04/2021 12:00:00 a. m. 400 C Payment 2021 5 -10 01/05/2021 12:00:00 a. m. 400 C Payment 2021 6 -10 01/06/2021 12:00:00 a. m. 400 C Payment 2021 7 -10 01/07/2021 12:00:00 a. m. 400 C Payment 2021 8 -10 01/08/2021 12:00:00 a. m. 400 C Payment 2021 9 -10 01/09/2021 12:00:00 a. m. 400 C Payment 2021 10 -10 01/10/2021 12:00:00 a. m. 400 C Payment 2021 11 -10 01/11/2021 12:00:00 a. m. 400 C Payment 2021 12 -10 01/12/2021 12:00:00 a. m. - MiguelSaldana77Resolver I
I can not paste the pbix , any idea why ?
- lbendlinSuper User
Only Super Users can attach files. Your sample data is ok, will check it later.