Circular Dependency Workaround

[Anonymous]

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 actually cause any errors if the code was executed, but Power BI prevents it from even trying.

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

DatesBetween is a column calculating how long it took a client to pay, based on the due date.

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

I've then simply created a measure to get the average of this.

Column = DATEADD(BILLING[Client Due Date],[Average Time to Pay],DAY)

Using these two, I want to add the average amount of time to project when future payments would be received, which is where the dependecy arises.

I've tried creating a duplicate Client Due Date column within the report, as well as at the Query level, with no success. 

PLZ HALP.

[CatalinaC]

Hi all, I'm trying to fix the below issue on my visual, where the months are listed randomly, but a circular depency error is preventing me from sorting by [Month Sort Order]

I have created the below table, one column at the time

dim_date_Practitioner =

CALENDAR(MIN('Training Fact'[Completion Date]), MAX('Training Fact'[Completion Date]))

 ----------------------------------------------------------------------------------------

FY Month = FORMAT(dim_date_Practitioner[Date], "mmm")

-----------------------------------------------------------------------------------------------

Month Sort Order =

MONTH(EDATE('dim_date_Practitioner'[Date],-3))

-----------------------------------------------------------------------------------------------

Fiscal Quarter =

"Q" & CEILING(

    MONTH(EDATE('dim_date_Practitioner'[Date], -3)),

    3

) / 3

Fiscal Quarter =

"Q" & CEILING(

    MONTH(EDATE('dim_date_Practitioner'[Date], -3)),

    3

) / 3

Fiscal Quarter =

"Q" & CEILING(

    MONTH(EDATE('dim_date_Practitioner'[Date], -3)),

    3

) / 3

Fiscal Quarter =

"Q" & CEILING(

    MONTH(EDATE('dim_date_Practitioner'[Date], -3)),

    3

) / 3

---------------------------------------------------------------------------------------------------------------

FY =

VAR CY = RIGHT(YEAR('dim_date_Practitioner'[Date]),2)

VAR LY = RIGHT(YEAR('dim_date_Practitioner'[Date])-1 ,2)

VAR NY = RIGHT(YEAR('dim_date_Practitioner'[Date])+1 ,2)

VAR FiscalYear =

IF(MONTH('dim_date_Practitioner'[Date]) >= 4,

CY & "-" & NY,

LY & "-" & CY

)

RETURN

FiscalYear

When I try to sort by month I get this error message

Any advice would be greatly appreciated!

[v-huizhn-msft · Accepted solution]

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]

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]

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