Bring previous balance

[Anonymous]

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?

[PaulDBrown]

@Anonymous 

You might want to explore the functions available, such as OPENINGBALANCEMONTH

https://dax.guide/openingbalancemonth/ 

[Anonymous]

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

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

[v-yiruan-msft]

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

[Anonymous · Accepted solution]

@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

[amitchandak]

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

[Anonymous]

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