Acumulate Formula with conditions

[MiguelSaldana77]

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?

[lbendlin]

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-...
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

[MiguelSaldana77]

I can not paste the pbix , any idea why ?

[lbendlin]

Here is some sample DAX code:

Initial Balance = 
var d= min([Date])
return calculate(sum([Amount]),[Date]<d)

Final Balance = 
var d= max([Date])
return calculate(sum([Amount]),[Date]<=d)

[lbendlin]

Only Super Users can attach files.  Your sample data is ok, will check it later.

[v-jingzhang]

Hi @lbendlin 

If you have time, can you help follow this thread? Thank you in advance. 

Best Regards,
Community Support Team _ Jing

[MiguelSaldana77]

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.