topic Re: Circular dependency workaround for Formula in DAX Commands and Tips

Circular dependency workaround for Formula

Auto_queen — Mon, 22 Aug 2022 23:32:06 GMT

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

Re: Circular dependency workaround for Formula

amitchandak — Tue, 23 Aug 2022 03:35:53 GMT

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

Re: Circular dependency workaround for Formula

tamerj1 — Tue, 23 Aug 2022 12:30:20 GMT

Hi Auto_queen
Please refer to attched sample file. Your actual case might be littele different depending in your real data and data model.

Re: Circular dependency workaround for Formula

Auto_queen — Tue, 23 Aug 2022 18:54:28 GMT

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

Re: Circular dependency workaround for Formula

Auto_queen — Tue, 23 Aug 2022 18:54:44 GMT

Thank you for your help! This worked too!

Re: Circular dependency workaround for Formula

Auto_queen — Tue, 23 Aug 2022 20:23:49 GMT

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