Forum Discussion

zoberender's avatar
zoberender
Frequent Visitor
3 years ago

Running Total with Multiple Columns

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 ...
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

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's avatar
zoberender
Frequent Visitor
3 years ago

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.