Forum Discussion
Helper Vneed help dax measure
i have 2 tables : budgetlines, and genLedgerEntries. budgetlines has budgetAmountACY, and genledgerentries has amount. i have a matrix with the following: rows: Category columns: Month values: BudgetAmountACY, amount, and variance. i want the following: i want to add a value, remaining amount: budget amount - amount. for january, the budget amount would be the normal budgetamountacy, and i get the remaining amount: ex: for january: budget amount: 2000 amount : 1000 for february: budget: 3000 amount : 2000 january remaining amount = 2000 - 1000 = 1000 that amount would be then added to the budget amount of february: february budget amount = 3000 + january remaining amount 1000 = 4000 february remaining amount = 4000 - 2000 = 2000 and so on. how can i do that in my current power bi setup?
Hi Hussein_charif ,
The circular dependency occurs when RemainingAmount is calculated based on AdjustedBudget, while AdjustedBudget also depends on RemainingAmount, creating a recursive loop that DAX cannot handle.
To prevent this, the CumulativePreviousRemaining measure should use only the raw budget and actuals from previous months with a SUMX + FILTER approach, without referencing AdjustedBudget or RemainingAmount. This keeps the carry-forward logic independent and avoids circular references.
Please check your logic to make sure no recursive dependency is present.
Thank you.
8 Replies
- bhanu_gautam
Super User
Hussein_charif First, you need to create a calculated column in your budgetlines table to calculate the remaining amount for each month
DAX
RemainingAmount =
VAR CurrentMonth = budgetlines[Month]
VAR CurrentCategory = budgetlines[Category]
VAR CurrentBudget = budgetlines[BudgetAmountACY]
VAR CurrentAmount =
CALCULATE(
SUM(genLedgerEntries[Amount]),
genLedgerEntries[Month] = CurrentMonth,
genLedgerEntries[Category] = CurrentCategory
)
VAR PreviousRemaining =
CALCULATE(
SUM(budgetlines[RemainingAmount]),
budgetlines[Month] = CurrentMonth - 1,
budgetlines[Category] = CurrentCategory
)
RETURN
IF(
ISBLANK(PreviousRemaining),
CurrentBudget - CurrentAmount,
(CurrentBudget + PreviousRemaining) - CurrentAmount
)After creating the measure, you can add it to your matrix visualization in Power BI. This will allow you to see the remaining amount for each month, taking into account the carryover from the previous month.
Hussein_charifhi bhanu_gautam , thank you for the reply. i have the remaining amount as a measure, not a calculated column, and it is correct. the problem is, when i want to adjust the budget amount in my matrix to be the previous month's remaining amount + the current month's bud
get, that adjustment needs to be done as well on the remaining amount measure to handle correctly the remaining amount, but with that, i get a circular dependancy, because i would be using the adjusted budget to + remaining amount to get the month's budget.
Month Budget Actual Adjusted Budget Remaining
Jan 2,000 1,000 2,000 1,000 Feb 3,000 2,000 4,000 2,000 Mar 4,000 3,000 6,000 3,000 this is an example of what i want, but i am unable to achieve it because of a circular dependancy where my remaining would have to be the adjusted budget(which uses remaining + budget) + budget
Hi Hussein_charif
Thank you for reaching out to the Microsoft fabric community forum. Thank you bhanu_gautam for your inputs on this issue.
After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used sample data on my end and successfully implemented it.
I am also including .pbix file for your better understanding, please have a look into it:
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you for using Microsoft Community Forum.- Hussein_charif
hi Anonymous , thank you for the reply. i tried your exact approach before, which was almost all correct except when i got to a circular dependancy when adjusting the cumulative remaining
Hi Hussein_charif ,
The circular dependency occurs when RemainingAmount is calculated based on AdjustedBudget, while AdjustedBudget also depends on RemainingAmount, creating a recursive loop that DAX cannot handle.
To prevent this, the CumulativePreviousRemaining measure should use only the raw budget and actuals from previous months with a SUMX + FILTER approach, without referencing AdjustedBudget or RemainingAmount. This keeps the carry-forward logic independent and avoids circular references.
Please check your logic to make sure no recursive dependency is present.
Thank you.
Hi Hussein_charif ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank you.
Hi Hussein_charif ,
I wanted to follow up on our previous suggestions. We would like to hear back from you to ensure we can assist you further.
Thank you.
Hi Hussein_charif ,
We haven’t received an update from you in some time. Could you please let us know if the issue has been resolved?
If you still require support, please let us know, we are happy to assist you.Thank you.
