Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Circular Dependency Workaround

I understand where my circular dependency is coming from, but I can't figure out a way to get the desired result without causing this error. And what makes it annoying is that I don't think it would ...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi Anonymous,

    I try to reproduce your scenario and create the following sample table. 



    1. Create calculated column using the same formula.

    DatesBetween = IF(ISBLANK(BILLING[Client Due Date]),BLANK(),(BILLING[Cash Deposit Date] - BILLING[Client Due Date])*1)


    2. Create a calculated column to get the average using the formula instead of measure. You'd better change it to int data type using INT function.

    Average Time to Pay = INT(AVERAGE(BILLING[DatesBetween]))


    3. Create a calculated column using the formula, and get expected result, please see the following screenshot.

    Column 2 = IF(ISBLANK(BILLING[Client Due Date]),BLANK(),BILLING[Client Due Date]+BILLING[Average Time to Pay])




    Please don't hesitate to ask if you have other issue.

    Thanks,
    Angelia

v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

Hi Anonymous,

I try to reproduce your scenario and create the following sample table. 



1. Create calculated column using the same formula.

DatesBetween = IF(ISBLANK(BILLING[Client Due Date]),BLANK(),(BILLING[Cash Deposit Date] - BILLING[Client Due Date])*1)


2. Create a calculated column to get the average using the formula instead of measure. You'd better change it to int data type using INT function.

Average Time to Pay = INT(AVERAGE(BILLING[DatesBetween]))


3. Create a calculated column using the formula, and get expected result, please see the following screenshot.

Column 2 = IF(ISBLANK(BILLING[Client Due Date]),BLANK(),BILLING[Client Due Date]+BILLING[Average Time to Pay])




Please don't hesitate to ask if you have other issue.

Thanks,
Angelia

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi v-huizhn-msft (Angelia),

     

    Thanks for the workaround! The only issue is that this calculates it once at the beginning, for all the clients. Our billing files contain records for every client. What I need is to dynamically calculate the average and THEN add it to the due date for an expected collection date.

     

    For instance, the overall average for all clients is cash deposit happens -1 from the due date. However, when I drill down to a specific client that on average pays 11 days after the due date, the expected collection date is still -1.

     

    Any help is greatly appreciated!

    • v-huizhn-msft's avatar
      v-huizhn-msft
      Microsoft Employee
      9 years ago

      Hi Anonymous,

      For your dynamical calculation, it sounds complex, could you please open a new thread and post some example for further analysis?

      Best Regards,
      Angelia