Forum Discussion

Live_Mace's avatar
Live_Mace
Frequent Visitor
6 months ago
Solved

Projected Available Balance Dax Calculation Help

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
    )

 

 

Please let me know if more information is required.

 

Thanks!

 

 

Need Help
  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    5 months ago

    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.

     

15 Replies

  • Live_Mace's avatar
    Live_Mace
    Frequent Visitor
    6 months ago

    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
    • krishnakanth240's avatar
      krishnakanth240
      Super User
      6 months ago

      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.

  • kushanNa's avatar
    kushanNa
    Super User
    6 months ago

    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?

    • Live_Mace's avatar
      Live_Mace
      Frequent Visitor
      6 months ago

      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.

       

  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    Community Support
    6 months ago

    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

    • Live_Mace's avatar
      Live_Mace
      Frequent Visitor
      6 months ago

      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.

      • v-kpoloju-msft's avatar
        v-kpoloju-msft
        Community Support
        6 months ago

        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.

  • krishnakanth240's avatar
    krishnakanth240
    Super User
    6 months ago

    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.

  • tharunkumarRTK's avatar
    tharunkumarRTK
    Super User
    6 months ago

    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!