Forum Discussion
Helper ICircular dependency workaround for Formula
Hello,
I'm new to Dax and I was wondering if there is a workaround for the circular dependency issue I'm having.
To calculate what a sales person's commission will be for the month, I take their YTD Sales payable amount and subtract their YTD salary and what commission they have already been paid for the year. If it is a negative number then they are paid $0.
This Month's Commission Payable = YTD Sales Payable Amount - (YTD Salary Paid + YTD Commissions Paid)
If This Month's Commission Payable < 0, then $0, else This Month's Commission Payable
YTD Commissions Paid = Running total of This Month's Commission Payable
The issue is that the YTD commission paid is based on this month's commission payable which is based on YTD commission paid, creating a circular dependency. Is there a workaround for this or another way to go about the formulas?
Below is some sample data.
| Month | YTD Sales Payable | YTD Salary Paid | YTD Commission Paid | This Month's Commission Payable |
| January | $322.73 | $5,000 | $0 | $0 |
| February | $12,219.74 | $10,000 | $0 | $2,219.74 |
| March | $25,402.15 | $15,000 | $2,219.74 | $8,182.41 |
| April | $48,177.31 | $20,000 | $10,402.15 | $17,775.16 |
| May | $52,058.58 | $25,000 | $28,177.31 | $0 |
| June | $52,505.53 | $30,000 | $28,177.31 | $0 |
| July | $59,360.60 | $35,000 | $28,177.31 | $1,183.29 |
Auto_queen , You need to calculate
if other are columns
This Month's Commission Payable =
var _date = eomonth([Date], -1)
var _value =Sum([YTD Sales Payable]) - Sum([YTD Salary Paid])
return calculate( if(_value>0, _value,0) , Filter(Table, eomonth(Table[Date],0) =_date))
in case others are measure
calculate([YTD Sales Payable]- [YTD Salary Paid], previousmonth('Date'[Date]) ) //use date table
as columns
YTD Commission Paid =
var _date = eomonth([Date], -1)
var _value =Sum([YTD Sales Payable]) - Sum([YTD Salary Paid])
return calculate( Sum( if(_value>0, _value,0) ), Filter(Table, eomonth(Table[Date],0) <=_date))
Hi Auto_queen
Please refer to attched sample file. Your actual case might be littele different depending in your real data and data model.
5 Replies
- amitchandak
Super User
Auto_queen , You need to calculate
if other are columns
This Month's Commission Payable =
var _date = eomonth([Date], -1)
var _value =Sum([YTD Sales Payable]) - Sum([YTD Salary Paid])
return calculate( if(_value>0, _value,0) , Filter(Table, eomonth(Table[Date],0) =_date))
in case others are measure
calculate([YTD Sales Payable]- [YTD Salary Paid], previousmonth('Date'[Date]) ) //use date table
as columns
YTD Commission Paid =
var _date = eomonth([Date], -1)
var _value =Sum([YTD Sales Payable]) - Sum([YTD Salary Paid])
return calculate( Sum( if(_value>0, _value,0) ), Filter(Table, eomonth(Table[Date],0) <=_date))
Auto_queenThank you for your help! This worked too!
- tamerj1
Community Champion
Hi Auto_queen
Please refer to attched sample file. Your actual case might be littele different depending in your real data and data model.- Auto_queen
Thank you for your help! The YTD Commission Paid wasn't populated, but I made a slight adjustment and it worked!
- Auto_queen
Actually, For YTD Commission Paid for June and July the amount should stay $28,177.31 since that is what has been paid - no more and no less...