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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

Need to create interdependent measures

Hi! I'm new to DAX measures - any help would be greatly appreciated! I need to calculate beginning and ending balances of a saving plan account. Thing is, they are dependent on each other. I was able to do this in excel in 2 minutes, but struggling with PowerBI measures. See below for a sample calculation I created in excel:

 

Sasank_Kiran_0-1693055153084.png

 

BOY Balance would be zero for the first year and

EOY balance = (first year contributions) minus (any benefits paid in the year) times (crediting rate appreciation)

BOY Balance for any following year would be its prior year's EOY Balance

 

My stab at the DAX formula (ending up with a circular dependency error):

 

BOY Bal = IF(MAX(Years[Time])=10CALCULATE([EOY Bal]FILTER(ALL(Years), Years[Time]=Years[Time]-1)))
EOY Bal =
IF(MAX(Years[Time])=1,
([Contribution Amount Per Year (Up-Front)]+[Contribution Amount Per Year (Periodic)]-[Benefit Payment Per Year (Total)])*(1+'Crediting Rate'[Crediting Rate Value]/100),
([BOY Bal New]+[Contribution Amount Per Year (Up-Front)]+[Contribution Amount Per Year (Periodic)]-[Benefit Payment Per Year (Total)])*(1+'Crediting Rate'[Crediting Rate Value]/100))
 
Thanks in advance!

 

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

Share the download link of your MS Excel workbook along with your formulas.  I will understand the logic from your formulas and try to translation them into DAX measures.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi Ashish! Thank you for replying!

Please see the excel workbook here containing calculations. 

 

Sasank

Hi,

That link takes me to a sign-in page.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

I would like to add a few more things here - 

  • all components used in this calculation are measures. I couldn't have them as calculated columns as their calculations depend on numeric range parameters which are entered at runtime in the dashboard.
  • The main issue here is that the beginning balance depends on ending balance from prior year and the ending balance depends on beginning balance of the current year.

I hope this clears up my calculation a bit. Thanks!

foodd
Super User
Super User

Would you please update your description and include source data that can be pasted into Excel or supply a sample .PBIX, and not as an image.  What you supplied is not complete.  If you are calling out Tables and columns or variables in DAX you intend to use in measures or columns, please ensure that the table and columns appear in the model.  As well, please ensure that you use a dedicated Dates table and that any necessary relationships are set up in your model.

 

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Anonymous
Not applicable

Hi! Thank you for taking time to provide the information! I will surely keep the points you mentioned in mind. I did go through a ton of posts before posting my question here. Unfortunately, I cannot share my PBIX file, however, I shared my excel workbook to another reply.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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