Circular dependency workaround for Formula

[Auto_queen]

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

[amitchandak · Accepted solution]

@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))

[tamerj1 · Accepted solution]

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]

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

[Auto_queen]

Thank you for your help! The YTD Commission Paid wasn't populated, but I made a slight adjustment and it worked! 

[amitchandak · Accepted solution]

@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_queen]

Thank you for your help! This worked too!