Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply

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

    KatieFarrand12_2-1701254026557.png

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

    KatieFarrand12_1-1701253547118.png

     

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

     

    KatieFarrand12_0-1701252593710.png

     

    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

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

 

 

View solution in original post

4 REPLIES 4
lbendlin
Super User
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.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

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

DateFeedTypeStorage_locationSiteOpening StockReceiptsConsumptionTransfered OutTransfered InDaily MovementClosing Stock
2/11/20232MM-AAZBakatekaLake21    021
2/11/20232MM-AAZBarge 2Lake330    0330
2/11/20232MM-AAZBarge 4Lake10    010
2/11/20232MM-NovatekBakatekaLake09   99
2/11/20232MM-NovatekBarge 2Lake019   1919
2/11/20232MM-NovatekBarge 4Lake01   11
2/11/20233MM-AAZBakatekaLake0130   130130
2/11/20233MM-AAZBarge 2Lake305  -2010-10295
2/11/20233MM-AAZBarge 4Lake130320  10330460
2/11/20234MM-AAZBarge 2Lake193370   370563
2/11/20234MM-AAZBarge 4Lake267    0267
2/11/20234MM-AAZBakatekaLake0160   160160
3/11/20232MM-AAZBakatekaLake21 9  930
3/11/20232MM-AAZBarge 2Lake33025-35  -10320
3/11/20232MM-AAZBarge 4Lake1035   3545
3/11/20232MM-NovatekBakatekaLake9    09
3/11/20232MM-NovatekBarge 2Lake19    019
3/11/20232MM-NovatekBarge 4Lake1193   193194
3/11/20233MM-AAZBakatekaLake13070 -80 -10120
3/11/20233MM-AAZBarge 2Lake295    0295
3/11/20233MM-AAZBarge 4Lake46099  80179639
3/11/20234MM-AAZBakatekaLake160480 -100 380540
3/11/20234MM-AAZBarge 2Lake563   100100663
3/11/20234MM-AAZBarge 4Lake267570   570837
4/11/20232MM-AAZBakatekaLake30160   160190
4/11/20232MM-AAZBarge 2Lake320 -53  -53267
4/11/20232MM-AAZBarge 4Lake45 -29  -2916
4/11/20232MM-NovatekBakatekaLake9    09
4/11/20232MM-NovatekBarge 2Lake199-28  -190
4/11/20232MM-NovatekBarge 4Lake194 -193  -1931
4/11/20233MM-AAZBakatekaLake120320   320440
4/11/20233MM-AAZBarge 2Lake295 -173  -173122
4/11/20233MM-AAZBarge 4Lake639320-147  173812
4/11/20234MM-AAZBakatekaLake540960   9601500
4/11/20234MM-AAZBarge 2Lake663 -374  -374289
4/11/20234MM-AAZBarge 4Lake837380-275  105942

 

If no Feed Type or Storage location, it should show like this:

Start Date2/11/2023
  
End Date4/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 Date2/11/2023
  
End Date4/11/2023
  
Feed Type4MM-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 Date2/11/2023
  
End Date4/11/2023
  
Feed Type 
Storage LocationBakateka
  
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 Date2/11/2023
  
End Date4/11/2023
  
Feed Type4MM-AAZ
Storage LocationBakateka
  
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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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