topic Re: Account statement with running total in DAX Commands and Tips

Account statement with running total

laphroaig76 — Wed, 26 Apr 2023 09:45:21 GMT

Hello community
I have connected our accounting system and now I want to map a bank statement in PowerBI.
I have the following columns: Date, posting text, amount, running total.
Now my current formula takes into account the posting text for the calculation of the running total. How can I filter this out so that it only creates the running total by date (date table) and amount?
The current formula is the following:
Amount YTD Test = CALCULATE (sum(buchung[betragbas]), DATESYTD ( 'Date'[Date] , "09/30"))

And this is the result:

betragbas *-1 = Betrag CHF, posting text = Buchungstext.

The date is a dax table connected with the effective booking date in the system.

Thank you!

Re: Account statement with running total

some_bih — Wed, 31 May 2023 12:05:52 GMT

Hi laphroaig76 if you already have Date table (with features in Power BI marked as Date table) then in you view use date column from that Date table, together with you measure. I hope this help.

Re: Account statement with running total

laphroaig76 — Wed, 05 Jul 2023 14:25:48 GMT

There is a proper Date table with all features in Power BI. Its name is Date'[Date]

I tried it with another formula and it works:

Saldo CHF = CALCULATE ( [Amount], FILTER(ALLSELECTED(buchung), buchung[Index] <= MAX(buchung[Index])))

I entered an index row and ordered it according this. But this solution is very slow. Any ideas?

Re: Account statement with running total

some_bih — Wed, 05 Jul 2023 16:44:01 GMT

Hi laphroaig76 if you simply need running total (rt), and your solution is slow, one alternative is to calulate rt in power query. Check link. Hope this help

https://gorilla.bi/power-query/running-total/

Re: Account statement with running total

laphroaig76 — Fri, 28 Jul 2023 14:29:35 GMT

It does not help. I have not only shirts in my table. I have dozens of accounts and account transactions in this journal and for whatever reason, when I include the posting text, if the texts are the same, it adds this together and if there are new texts, it starts adding back at zero.

Re: Account statement with running total

laphroaig76 — Wed, 02 Aug 2023 14:09:12 GMT

Without Text it works properly

But with Text not, why?