Calculating Dynamic Balances Over a Period

[Katie-Farrand12]

Hello all,

We have a model that tracks transactions of feed. We have calculated opening & closing daily balances based on what is being received and consumed. The balances are dynamic to our slicers -  "Storage_location" and "feed_type".

I have 2 opening stock measures:

1. Opening Stock by Day: this is working perfectly, it is dynamic to the slicers "Storage_location" and "feed_type". 
2. Opening Stock for a period: If the dates are filtered to 2nd November - 6 November, this needs to show opening stock on 2nd November. This is working, however it is not dynamic to the slicers "Storage_location" and "Feed_type"

   1. Opening Stock By Day - Detail

   Please see below visualisation & DAX for the measure working perfeclty. Note - Bakateka and Barge 2 are "storage_location", the other info is "feed_type"

   Just over the days

   

   Within 1 day by "Storage_Location" by "Feed Type"  (snippet)

   

   DAX

   Opening Stock = VAR StorageLocation='Barge Balances'[Storage_location]

   VAR CurrentDate = 'Barge Balances'[Date]

   VAR CurrentFeedType = 'Barge Balances'[FeedType]

   RETURN

       CALCULATE(

           SUM('Barge Balances'[Daily Movement]),-

           FILTER(

               ALL('Barge Balances'),

               'Barge Balances'[Date] < CurrentDate &&

               'Barge Balances'[FeedType] = CurrentFeedType && 'Barge Balances'[Storage_location]=StorageLocation)   )

   2. Opening Stock for a period - Detail

   The below picture shows cards for details over period 2 - 6 November. You can see that the opening stock is for 2 November.  Closing Stock is calculated as [Opening Stock 2 Nov] + [Sum Receipts over the period] - [Sum Consumption Over Period]  = [Closing Stock 6 Nov].

   

   The Problem is that when I filter this for "Storage_Location" or "Feed_type",  the number just remains as the total, rather than the opening balance only for that storage location or feed type. 

   Current DAX

   First Day Opening Stock =

   VAR FilteredStartDate = MIN('Barge Balances'[Date])

   RETURN

       COALESCE(

           CALCULATE(

               [Barge Opening Balance],

               FILTER(

                   ALL('Barge Balances'),

                   'Barge Balances'[Date]= FilteredStartDate  )      ),  0   )

   Issue with this DAX

   It will not allow me to create variables for "storage_location" and "feed_Type" like in the first measure. 

   Can anyone please help me write DAX to calculate the opening balance over a period that is dynamic to the slicers?

   Thank you,

   Katie

[lbendlin]

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-...

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/1447...

[Katie-Farrand12]

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.

[lbendlin · Accepted solution]

I think you made your life harder than necessary.  See attached.

[Katie-Farrand12]

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