Forum Discussion
Projected Available Balance Dax Calculation Help
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.
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
|