Running Total with Multiple Columns

[zoberender]

Hi PBI Community, 

I have a dataset where I have a Category, Date, Transaction # and Amount.  I also have a date table.  I am trying to create matrix that rolls forward the ending balance to the next day.  

A sample dataset is:

Date	Transaction #	Category	Amount
10/03/2022	OB	Opening Balance	9
10/03/2022	2uyg4	Debit	12
10/03/2022	6scq9	Debit	7
10/03/2022	z2dt1	Credit	-9
10/03/2022	eu3x0	Credit	-12
10/04/2022	s1c4d	Debit	7
10/04/2022	h89vg	Credit	-12
10/04/2022	au7fi	Debit	7
10/04/2022	xrltp	Debit	9
10/05/2022	f0sox	Credit	-12
10/05/2022	sj2ya	Debit	12
10/05/2022	oupny	Debit	9
10/05/2022	c9toh	Credit	-7
10/05/2022	jl8zm	Credit	-9
10/05/2022	5eta6	Debit	12
10/05/2022	04f29	Credit	-7

Desired Output:

Date	Prior Day End	Opening Balance	Debit	Credit	End Balance
10/03/2022	0	9	19	-21	7
10/04/2022	7		23	-12	18
10/05/2022	18		33	-35	16

When I create the matrix, I use Date for the rows and Category for the columns.  I then have a couple measures:

Total Amount = sum('Final Data'[Amount])

Running Total = 
CALCULATE (
    [Total Amount], 
    FILTER(
        ALL ( 'Date' ), 
        'Date'[Date] <= MAX ('Date'[Date] )
    )
)

I can put the Total Amount in the Matrix and I get columns with the different categories like expected.  But I can't figure out how to get the Running Total into the matrix as one column.  If I add it in the values section I get a column for each Category.  I really only want the total for the day, not for each column.  

I have searched all over and tried a bunch of different things, but I just can't figure out how to do this!  Any help would be appreciated.  

[amitchandak]

@zoberender , Thsi should very similar to how we build inventory

examples

Inventory / OnHand
[Intial Inventory] + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

Inventory / OnHand
CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),all('Date')) + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

Power BI Inventory On Hand
Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw

[zoberender]

Thanks for the response Amit.  I watched the video and your example is slightly different than mine but a similar concept. The main difference is that you have separate tables for sales, purchases and inventory which allows you to put them in individual columns of the matrix.  My category is one column of a table that I'm using in the matrix.  

I simplified the data to provide an example.  In reality, the category column has many different values (interest received, maturities, returns, debits, credits etc).  While I could write individual measures to calculate the amount of each category, this is not optimal as there could be "new" categories come in as the data is refreshed.