Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Auto_queen
Helper I
Helper I

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

 

MonthYTD Sales Payable  YTD Salary PaidYTD Commission PaidThis 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
2 ACCEPTED SOLUTIONS
amitchandak
Super User
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))

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

tamerj1
Super User
Super User

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

1.png2.png

View solution in original post

5 REPLIES 5
tamerj1
Super User
Super User

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

1.png2.png

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! 

amitchandak
Super User
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))

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Thank you for your help! This worked too! 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.