Forum Discussion

MiguelSaldana77's avatar
MiguelSaldana77
Resolver I
3 years ago

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

    • MiguelSaldana77's avatar
      MiguelSaldana77
      Resolver I
      AccountTCKind_MovementYearMonthAmountDateChargers_January
      100ACharge202012001/01/2020 12:00:00 a. m. 
      100ACharge202022001/02/2020 12:00:00 a. m. 
      100ACharge202032001/03/2020 12:00:00 a. m. 
      100ACharge202042001/04/2020 12:00:00 a. m. 
      100ACharge202052001/05/2020 12:00:00 a. m. 
      100ACharge202062001/06/2020 12:00:00 a. m. 
      100ACharge202072001/07/2020 12:00:00 a. m. 
      100ACharge202082001/08/2020 12:00:00 a. m. 
      100ACharge202092001/09/2020 12:00:00 a. m. 
      100ACharge2020102001/10/2020 12:00:00 a. m. 
      100ACharge2020112001/11/2020 12:00:00 a. m. 
      100ACharge2020122001/12/2020 12:00:00 a. m. 
      100APayment20201-1001/01/2020 12:00:00 a. m. 
      100APayment20202-1001/02/2020 12:00:00 a. m. 
      100APayment20203-1001/03/2020 12:00:00 a. m. 
      100APayment20204-1001/04/2020 12:00:00 a. m. 
      100APayment20205-1001/05/2020 12:00:00 a. m. 
      100APayment20206-1001/06/2020 12:00:00 a. m. 
      100APayment20207-1001/07/2020 12:00:00 a. m. 
      100APayment20208-1001/08/2020 12:00:00 a. m. 
      100APayment20209-1001/09/2020 12:00:00 a. m. 
      100APayment202010-1001/10/2020 12:00:00 a. m. 
      100APayment202011-1001/11/2020 12:00:00 a. m. 
      100APayment202012-1001/12/2020 12:00:00 a. m. 
      100ACharge202112001/01/2021 12:00:00 a. m. 
      100ACharge202122001/02/2021 12:00:00 a. m. 
      100ACharge202132001/03/2021 12:00:00 a. m. 
      100ACharge202142001/04/2021 12:00:00 a. m. 
      100ACharge202152001/05/2021 12:00:00 a. m. 
      100ACharge202162001/06/2021 12:00:00 a. m. 
      100ACharge202172001/07/2021 12:00:00 a. m. 
      100ACharge202182001/08/2021 12:00:00 a. m. 
      100ACharge202192001/09/2021 12:00:00 a. m. 
      100ACharge2021102001/10/2021 12:00:00 a. m. 
      100ACharge2021112001/11/2021 12:00:00 a. m. 
      100ACharge2021122001/12/2021 12:00:00 a. m. 
      100APayment20211-1001/01/2021 12:00:00 a. m. 
      100APayment20212-1001/02/2021 12:00:00 a. m. 
      100APayment20213-1001/03/2021 12:00:00 a. m. 
      100APayment20214-1001/04/2021 12:00:00 a. m. 
      100APayment20215-1001/05/2021 12:00:00 a. m. 
      100APayment20216-1001/06/2021 12:00:00 a. m. 
      100APayment20217-1001/07/2021 12:00:00 a. m. 
      100APayment20218-1001/08/2021 12:00:00 a. m. 
      100APayment20219-1001/09/2021 12:00:00 a. m. 
      100APayment202110-1001/10/2021 12:00:00 a. m. 
      100APayment202111-1001/11/2021 12:00:00 a. m. 
      100APayment202112-1001/12/2021 12:00:00 a. m. 
      400CCharge202012001/01/2020 12:00:00 a. m. 
      400CCharge202022001/02/2020 12:00:00 a. m. 
      400CCharge202032001/03/2020 12:00:00 a. m. 
      400CCharge202042001/04/2020 12:00:00 a. m. 
      400CCharge202052001/05/2020 12:00:00 a. m. 
      400CCharge202062001/06/2020 12:00:00 a. m. 
      400CCharge202072001/07/2020 12:00:00 a. m. 
      400CCharge202082001/08/2020 12:00:00 a. m. 
      400CCharge202092001/09/2020 12:00:00 a. m. 
      400CCharge2020102001/10/2020 12:00:00 a. m. 
      400CCharge2020112001/11/2020 12:00:00 a. m. 
      400CCharge2020122001/12/2020 12:00:00 a. m. 
      400CPayment20201-1001/01/2020 12:00:00 a. m. 
      400CPayment20202-1001/02/2020 12:00:00 a. m. 
      400CPayment20203-1001/03/2020 12:00:00 a. m. 
      400CPayment20204-1001/04/2020 12:00:00 a. m. 
      400CPayment20205-1001/05/2020 12:00:00 a. m. 
      400CPayment20206-1001/06/2020 12:00:00 a. m. 
      400CPayment20207-1001/07/2020 12:00:00 a. m. 
      400CPayment20208-1001/08/2020 12:00:00 a. m. 
      400CPayment20209-1001/09/2020 12:00:00 a. m. 
      400CPayment202010-1001/10/2020 12:00:00 a. m. 
      400CPayment202011-1001/11/2020 12:00:00 a. m. 
      400CPayment202012-1001/12/2020 12:00:00 a. m. 
      400CCharge202114001/01/2021 12:00:00 a. m. 
      400CCharge202122001/02/2021 12:00:00 a. m. 
      400CCharge202132001/03/2021 12:00:00 a. m. 
      400CCharge202142001/04/2021 12:00:00 a. m. 
      400CCharge202152001/05/2021 12:00:00 a. m. 
      400CCharge202162001/06/2021 12:00:00 a. m. 
      400CCharge202172001/07/2021 12:00:00 a. m. 
      400CCharge202182001/08/2021 12:00:00 a. m. 
      400CCharge202192001/09/2021 12:00:00 a. m. 
      400CCharge2021102001/10/2021 12:00:00 a. m. 
      400CCharge2021112001/11/2021 12:00:00 a. m. 
      400CCharge2021122001/12/2021 12:00:00 a. m. 
      400CPayment20211-1001/01/2021 12:00:00 a. m. 
      400CPayment20212-1001/02/2021 12:00:00 a. m. 
      400CPayment20213-1001/03/2021 12:00:00 a. m. 
      400CPayment20214-1001/04/2021 12:00:00 a. m. 
      400CPayment20215-1001/05/2021 12:00:00 a. m. 
      400CPayment20216-1001/06/2021 12:00:00 a. m. 
      400CPayment20217-1001/07/2021 12:00:00 a. m. 
      400CPayment20218-1001/08/2021 12:00:00 a. m. 
      400CPayment20219-1001/09/2021 12:00:00 a. m. 
      400CPayment202110-1001/10/2021 12:00:00 a. m. 
      400CPayment202111-1001/11/2021 12:00:00 a. m. 
      400CPayment202112-1001/12/2021 12:00:00 a. m. 
      • lbendlin's avatar
        lbendlin
        Super User

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