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

CatalinaC's avatar
CatalinaC
New Member
2 years ago

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!