Running balance total

[Drobinson1]

I have a current inventory quantity and wanted to be able to calculate balances throughout time based on historical movement quantities.

In this example I know the current starting quantity of 7.  I also know all of the movements since a certain archive point.

I would like to calculate the ending/starting balance.  Again all I have is the current balance on hand as of right now and three columns of part code/date/ic_move_quantity.

I want to calculate the starting and ending balances after each ransaction.

to do this I think I need to find what the most recent transation was and go backwards from there.   I am using direct query and can' figure out how to do this since I can't use variables and calcualte in a column.

Part Code	Date	Ic_Move_Quantity	Starting Balance	Ending
BUMESPEP	10/20/2017	-96	103	7
BUMESPEP	10/19/2017	-25	128	103
BUMESPEP	10/18/2017	128	0

[v-huizhn-msft]

Hi @Drobinson1,

You can not create measure or calculated column when you are using direct query, I personally suggest you use import mode. And just import the necessary data. Then create measure as the @Ashish_Mathur posted. Please feel free to ask if you have any other issue.

Best Regards,
Angelia

[Ashish_Mathur]

Hi @Drobinson1,

You may refeer to my solution in this workbook.

Hope this helps.

[Anonymous]

Take a look at this:

http://www.daxpatterns.com/cumulative-total/

These starting and ending values can be calculates as measures.

I would caution using DirectQuery.  Do you need the near-realtime capability?  The performance will likely suffer, and you're right, you don't get to use many of DAX's more powerful functions.

https://www.sqlbi.com/tv/directquery-in-analysis-services-best-practices-performance-use-cases/ 

Marco Russo speaks about how and when to use DirectQuery.  Maybe you'd be better off with a Live Connection instead?