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

     

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

      • Live_Mace's avatar
        Live_Mace
        Frequent Visitor
        5 months ago

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

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