Forum Discussion
Running balance total
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 |
3 Replies
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?
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
