Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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:

 

 

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])=1, 0, CALCULATE([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!

 

Need Help

6 Replies

  • foodd's avatar
    foodd
    Community Champion
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Ashish! Thank you for replying!

      Please see the excel workbook here containing calculations. 

       

      Sasank

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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!