Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Bring previous balance

Hi guys

I have many rows, with the account, account description, debits, credits, and the initial balance and the final balance, so my query for the initial balance is:

Starting balance =
VAR __Account = MAX([CTA_CONTABLE])
VAR __MinDate - MINX(FILTER('4 Balances','4 Balances'[CTA_CONTABLE] - __Account),[DATE])
RETURN
(MINX(FILTER('4 Balances','4 Balances'[CTA_CONTABLE] = __Account && [CLOSE] = __MinDate),[SALDO_INICIAL]))
With this I bring the initial balance of the last register (order by dates), because each raw is for a debit or a credit.
My problem is when I filter for dates, if the last movement of the the account "xx-xx-xx" was on april 2019, and in 2020 I don't have any movements, it will never be shown with this filter. How could I indicate that I want to continue showing the accounts with the final balance <>0, in order to have the real balance in each filtered period?
  • Anonymous's avatar
    Anonymous
    5 years ago

    @yingyinr Hi, I fixed it with:

    Saldo inicial1 =
    VAR __Account = MAX([CTA_CONTABLE])
    VAR __MinDate = MINX(FILTER('3Saldo ini','3Saldo ini'[CTA_CONTABLE] = __Account),[PERIODO])
    RETURN
    CALCULATE(SUM('3Saldo ini'[SALDO_INICIAL]),filter( '3Saldo ini','3Saldo ini'[CTA_CONTABLE] = __Account && [PERIODO] = __MinDate ))
     
    I was selecting wrong the columns
    Thank you so much
     

    Kind regards

    Eduardo Silva

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      amitchandak hey, sure:

      for example, this account is an asset, when I filter by date, selecting 2019, everything is ok, because my date for this row is 2019, but when I filter any month or the complete year 2020, the problem is that the system doesnt found that account in those dates and my sum of assets doesnt take that account. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      @PaulDBrown thank you Paul, but how could I applied it if I could filtered by different periods of time

      eduardosilvin_0-1601638484286.png

      (account - open balance - debits - credits - final balance)

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Anonymous ,

        I'm sorry that I still have some confusion about your requirement. Could you please provide more sample data and explain your problem&desired result with specific data examples? And could you please list the fields/measures which applied in Matrix and slicer? Are they from the same table?

        My problem is when I filter for dates, if the last movement of the the account "xx-xx-xx" was on april 2019, and in 2020 I don't have any movements, it will never be shown with this filter. How could I indicate that I want to continue showing the accounts with the final balance <>0, in order to have the real balance in each filtered period?

        Best Regards

        Rena