Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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
Hi @Drobinson1,
You may refeer to my solution in this workbook.
Hope this helps.
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |