cancel
Showing results for
Did you mean:

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.

Helper I

## Calculating Dynamic Balances Over a Period

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

1 ACCEPTED SOLUTION
Super User

4 REPLIES 4
Super User

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.

Helper I

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.

Super User

Helper I

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors