Forum Discussion
Calculating Dynamic Balances Over a Period
I think you made your life harder than necessary. See attached.
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Hi lbendlin thank you for the response. I unfortunately cannot attach an XLSX form. Below is a balances table that I have built based on transactional data in Power BI (dummy data). The opening stock is what I am struggling with.
Data
Date FeedType Storage_location Site Opening Stock Receipts Consumption Transfered Out Transfered In Daily Movement Closing Stock 2/11/2023 2MM-AAZ Bakateka Lake 21 0 21 2/11/2023 2MM-AAZ Barge 2 Lake 330 0 330 2/11/2023 2MM-AAZ Barge 4 Lake 10 0 10 2/11/2023 2MM-Novatek Bakateka Lake 0 9 9 9 2/11/2023 2MM-Novatek Barge 2 Lake 0 19 19 19 2/11/2023 2MM-Novatek Barge 4 Lake 0 1 1 1 2/11/2023 3MM-AAZ Bakateka Lake 0 130 130 130 2/11/2023 3MM-AAZ Barge 2 Lake 305 -20 10 -10 295 2/11/2023 3MM-AAZ Barge 4 Lake 130 320 10 330 460 2/11/2023 4MM-AAZ Barge 2 Lake 193 370 370 563 2/11/2023 4MM-AAZ Barge 4 Lake 267 0 267 2/11/2023 4MM-AAZ Bakateka Lake 0 160 160 160 3/11/2023 2MM-AAZ Bakateka Lake 21 9 9 30 3/11/2023 2MM-AAZ Barge 2 Lake 330 25 -35 -10 320 3/11/2023 2MM-AAZ Barge 4 Lake 10 35 35 45 3/11/2023 2MM-Novatek Bakateka Lake 9 0 9 3/11/2023 2MM-Novatek Barge 2 Lake 19 0 19 3/11/2023 2MM-Novatek Barge 4 Lake 1 193 193 194 3/11/2023 3MM-AAZ Bakateka Lake 130 70 -80 -10 120 3/11/2023 3MM-AAZ Barge 2 Lake 295 0 295 3/11/2023 3MM-AAZ Barge 4 Lake 460 99 80 179 639 3/11/2023 4MM-AAZ Bakateka Lake 160 480 -100 380 540 3/11/2023 4MM-AAZ Barge 2 Lake 563 100 100 663 3/11/2023 4MM-AAZ Barge 4 Lake 267 570 570 837 4/11/2023 2MM-AAZ Bakateka Lake 30 160 160 190 4/11/2023 2MM-AAZ Barge 2 Lake 320 -53 -53 267 4/11/2023 2MM-AAZ Barge 4 Lake 45 -29 -29 16 4/11/2023 2MM-Novatek Bakateka Lake 9 0 9 4/11/2023 2MM-Novatek Barge 2 Lake 19 9 -28 -19 0 4/11/2023 2MM-Novatek Barge 4 Lake 194 -193 -193 1 4/11/2023 3MM-AAZ Bakateka Lake 120 320 320 440 4/11/2023 3MM-AAZ Barge 2 Lake 295 -173 -173 122 4/11/2023 3MM-AAZ Barge 4 Lake 639 320 -147 173 812 4/11/2023 4MM-AAZ Bakateka Lake 540 960 960 1500 4/11/2023 4MM-AAZ Barge 2 Lake 663 -374 -374 289 4/11/2023 4MM-AAZ Barge 4 Lake 837 380 -275 105 942 If no Feed Type or Storage location, it should show like this:
Start Date 2/11/2023 End Date 4/11/2023 Feed Type Storage Location Opening Stock 1,256 Receipts 4,630 Consumption - 1,298 Transfered Out - 200 Transfered In 200 Daily Movement 3,332 Closing Stock 4,588 If a Feed type is selected, it should look like this:
Start Date 2/11/2023 End Date 4/11/2023 Feed Type 4MM-AAZ Storage Location Opening Stock 460 Receipts 2,920 Consumption - 649 Transfered Out - 100 Transfered In 100 Daily Movement 2,271 Closing Stock 2,731 IF storage type is selected, it should look like this
Start Date 2/11/2023 End Date 4/11/2023 Feed Type Storage Location Bakateka Opening Stock 21 Receipts 2,289 Consumption 9 Transfered Out - 180 Transfered In - Daily Movement 2,118 Closing Stock 2,139 If both are selected, it should look like this
Start Date 2/11/2023 End Date 4/11/2023 Feed Type 4MM-AAZ Storage Location Bakateka Opening Stock - Receipts 1,600 Consumption - Transfered Out - 100 Transfered In - Daily Movement 1,500 Closing Stock 1,500 OPening Stock is based on the starts date. Closing Stock is based on the End date. Movement is based on SUM of movement in the time period (2 Nov - 4 Nov). The measure I built is wokring on the date filters but not the "feed Type" and "Storage Location" filters.
I think you made your life harder than necessary. See attached.
Hello lbendlin
You are a wonderful human being! Thank you so much for taking the time to help me with this. You are 100% correct, I was making it far too complicated. I knew it was possible!
Have a wonderful day.
Regards,
Katie
