Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Vote for your favorite vizzies from the Power BI Dataviz World Championship submissions. Vote now!

Reply
Live_Mace
Frequent Visitor

Projected Available Balance Dax Calculation Help

‎02-08-2026 07:46 AM

Hello!

 

So...I have 3 fact tables(Inventory, Production and Demand) and 2 dim tables (Dates and Item). Each of the fact tables has a many to one with relationship with the dim tables. I'm representing this data on a Matrix visual.


The problem is that I can't get my dax measure to not carry over negative values.


Here's is the default measure I have, and here's a screenshot to show what I want to achieve.

Beginning Inventory =
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR MaxOnHandDate =
    CALCULATE (
        MAX ( 'Date'[Date] ),
        FILTER (
            ALLSELECTED ( Inventory ),
            NOT ISBLANK ( Inventory[Inventory] )
        )
    )
VAR LastBI =
    CALCULATE (
        SUM ( Inventory[Inventory] ),
        FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Date] = MaxOnHandDate )
    )
VAR DatesBetweenMaxDates =
    FILTER (
        ALLSELECTED ( 'Date' ),
        'Date'[Date] < MaxDate
            && 'Date'[Date] >= MaxOnHandDate
    )
VAR Projected_BI =
    CALCULATE (
        LastBI + SUM ( Production[Production] ) - SUM ( Demand[Demand] ),
        DatesBetweenMaxDates
    )
RETURN
    IF (
        NOT ISBLANK ( SUM ( Inventory[Inventory] ) ),
        SUM ( Inventory[Inventory] ),
        Projected_BI
    )

 

Live_Mace_1-1770565295129.png

 

Please let me know if more information is required.

 

Thanks!

 

 

Labels:
Message 1 of 15
585 Views
14 REPLIES 14
v-kpoloju-msft
Community Support
Community Support

Tuesday

Hi @Live_Mace, 

Thank you for reaching out to the Microsoft fabric community forum. Also, thanks to @tharunkumarRTK, @kushanNa, @krishnakanth240, for those inputs. I reproduced the scenario again, and it worked on my end. I used it as sample data and successfully implemented it.

 

I am also including .pbix file for your better understanding, please have a look into it.

 

Hope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.

Thank you for using the Microsoft Fabric Community Forum

5000501.pbix
Message 11 of 15
318 Views
Live_Mace
Frequent Visitor
In response to v-kpoloju-msft

Tuesday

Thanks @tharunkumarRTK that perfectly explains it.


@v-kpoloju-msft thanks for your help as well, however your solution still isn't working as expected, it's not resetting the value, it's just displaying the negatives as 0. Please can you reproduce with the entire sample data? That will give a clearer picture of what the issue is. I can't find the option to upload the pbix.

Message 12 of 15
300 Views
v-kpoloju-msft
Community Support
Community Support
In response to Live_Mace

Thursday

Hi @Live_Mace,
Thank you for the update.

Could you please upload your PBIX file to a secure file-sharing location (for example, OneDrive) and share a view/download link here in the thread? That would allow us to review the exact model, relationships, and measure logic and reproduce the scenario more accurately on our end.

If sharing the full file isn’t possible, alternatively you can share screenshots of the model view (relationships diagram) and the exact DAX measure currently being used. That will also help us narrow down where the behavior is differing from the expected result.

Thank you for using the Microsoft Fabric Community Forum.

Message 13 of 15
185 Views
0
Live_Mace
Frequent Visitor
In response to v-kpoloju-msft

Thursday

@v-kpoloju-msft I've uploaded the pbix here

Please let me know if you need any more information. Thanks

Message 14 of 15
175 Views
0
v-kpoloju-msft
Community Support
Community Support
In response to Live_Mace

yesterday

Hi @Live_Mace, 

Thank you for sharing the PBIX file that helped clarify the behaviour. The current measure is performing a recursive running balance, which means when the inventory becomes negative, that negative value is still being used as the starting point for the next date. This is why you’re seeing values recover from negative balances instead of resetting to zero before applying production and demand.

This requirement involves a sequential calculation where each row depends on the previously calculated result. DAX is a set-based language and does not support iterative row-by-row state logic, so it cannot reliably reset the balance before calculating the next row. The recommended approach is to compute this projection in Power Query (or the source system), where rows can be processed sequentially and the reset logic (MAX(0, PreviousInventory)) can be applied correctly. Once calculated in Power Query, the result can be used directly in the model and will display the expected behaviour in the matrix visual.

Hope that clarifies. Let us know if you have any doubts regarding this. We will be happy to help.

Thank you for using the Microsoft Fabric Community Forum.

 

Message 15 of 15
18 Views
0
tharunkumarRTK
Super User
Super User

Monday

@Live_Mace 

As far as I understand, in your formula (Beginning Inventory + Production − Demand), the Beginning Inventory should reset to 0 at the first instance where the result becomes negative, and that reset value should then be reused as the Beginning Inventory for subsequent rows until the cycle repeats.

This would require the DAX expression to persist and reuse its own previously calculated result across all rows, which is not something DAX supports in measures, visual calculations, or calculated columns.

Please correct me if my understanding is incorrect.

 

 

 

Connect on LinkedIn

read my blogs here: techietips.co.in

 

 








Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos.

Proud to be a Super User!
PBI_SuperUser_Rank@2x.png
Message 10 of 15
338 Views
0
kushanNa
Super User
Super User

Monday

Hi @Live_Mace 

 

Something is not clear to me. You mentioned “not carry over negative values,” but your example screenshot shows negative values. Have I misunderstood your requirement, or am I missing something here?

Message 6 of 15
410 Views
Live_Mace
Frequent Visitor
In response to kushanNa

Monday

Hello @kushanNa,

the "Beginning Inventory" is what I have currently, I'm trying to achieve the "Expected Beginning Inventory" that one doesn't carry over the negative values. I've attached another screenshot highlighing it.

Live_Mace_0-1770650038571.png

 

Message 7 of 15
403 Views
0
kushanNa
Super User
Super User
In response to Live_Mace

Monday

@Live_Mace 

 

Oh okay, please check if the attached result is you are looking for ? 

Projected.pbix
Message 8 of 15
388 Views
0
Live_Mace
Frequent Visitor
In response to kushanNa

Monday

Thanks @kushanNa However It's still not calculating correctly, we shouldn't be showing 0 in these dates: 

Live_Mace_0-1770661525733.png

 

Also in the sample data we only have inventory on 2026-02-07 and 2026-02-08.

 

Message 9 of 15
376 Views
0
Live_Mace
Frequent Visitor

‎02-08-2026 05:44 PM

@krishnakanth240 Please refer to screenshot in the original post 

Message 5 of 15
461 Views
0
Live_Mace
Frequent Visitor

‎02-08-2026 09:37 AM

@krishnakanth240 I tried using the max, it shows 0, but behind the scenes it's still summing up the negatives.
Here's the sample data:

Dim_Dates Dim_Item Fact_Inventory   Fact_Production   Fact_Demand  
2026-02-07 A ItemDateInventory ItemDateProduction ItemDateDemand
2026-02-08 B A2026-02-0717040 A2026-02-075,731 A2026-02-0712,537
2026-02-09 C A2026-02-0816781 A2026-02-08  A2026-02-083,160
2026-02-10   A2026-02-09  A2026-02-09  A2026-02-093,402
2026-02-11   A2026-02-10  A2026-02-100 A2026-02-107,434
2026-02-12   A2026-02-11  A2026-02-110 A2026-02-115,922
2026-02-13   A2026-02-12  A2026-02-125,198 A2026-02-120
2026-02-14   A2026-02-13  A2026-02-130 A2026-02-133,703
2026-02-15   A2026-02-14  A2026-02-140 A2026-02-143,703
2026-02-16   A2026-02-15  A2026-02-150 A2026-02-153,703
2026-02-17   A2026-02-16  A2026-02-165,202 A2026-02-163,703
    A2026-02-17  A2026-02-175,197 A2026-02-173,703
    B2026-02-07421 B2026-02-07  B2026-02-070
    B2026-02-08858 B2026-02-08  B2026-02-080
    B2026-02-09  B2026-02-09  B2026-02-090
    B2026-02-10  B2026-02-10  B2026-02-100
    B2026-02-11  B2026-02-11  B2026-02-11400
    B2026-02-12  B2026-02-12  B2026-02-12500
    B2026-02-13  B2026-02-13  B2026-02-130
    B2026-02-14  B2026-02-14  B2026-02-140
    B2026-02-15  B2026-02-15  B2026-02-15524
    B2026-02-16  B2026-02-161,000 B2026-02-16524
    B2026-02-17  B2026-02-17  B2026-02-17524
    C2026-02-07349895 C2026-02-0731,752 C2026-02-0729,161
    C2026-02-08362009 C2026-02-08  C2026-02-0812,827
    C2026-02-09  C2026-02-09  C2026-02-0912,827
    C2026-02-10  C2026-02-1045,464 C2026-02-1017,442
    C2026-02-11  C2026-02-110 C2026-02-11122,827
    C2026-02-12  C2026-02-120 C2026-02-12335,811
    C2026-02-13  C2026-02-1331,258 C2026-02-1316,254
    C2026-02-14  C2026-02-1424,883 C2026-02-1418,144
    C2026-02-15  C2026-02-15  C2026-02-1513,581
    C2026-02-16  C2026-02-16  C2026-02-1613,581
    C2026-02-17  C2026-02-17  C2026-02-1713,581
Message 3 of 15
523 Views
krishnakanth240
Power Participant
Power Participant
In response to Live_Mace

‎02-08-2026 05:30 PM

Hi @Live_Mace 

Can you share the screenshot how it is summing up negative values. Could you please elaborate what exact output value you are looking for.

 

Above sample data not sufficient to work. Could you please share the sample data of individual Fact and Dim tables to work or pbix file.

Message 4 of 15
467 Views
0
krishnakanth240
Power Participant
Power Participant

‎02-08-2026 08:41 AM

Hi @Live_Mace 

From the above measure, in return statement can you replace 'Projected_BI' with MAX(0, Projected_BI) and check where negative values gets replaced with 0.

 

Also, can you share all the Fact and Dim tables with sample data of records in text format and fields you are using into the matrix visual.

Message 2 of 15
541 Views
0

Helpful resources

Announcements
Power BI DataViz World Championships

Power BI Dataviz World Championships

Vote for your favorite vizzies from the Power BI World Championship submissions!

Sticker Challenge 2026 Carousel

Join our Community Sticker Challenge 2026

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

January Power BI Update Carousel

Power BI Monthly Update - January 2026

Check out the January 2026 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All