Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
Solved! Go to 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))
Hi @Auto_queen
Please refer to attched sample file. Your actual case might be littele different depending in your real data and data model.
Hi @Auto_queen
Please refer to attched sample file. Your actual case might be littele different depending in your real data and data model.
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...
Thank you for your help! The YTD Commission Paid wasn't populated, but I made a slight adjustment and it worked!
@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))
Thank you for your help! This worked too!
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |