Circular dependency was detected

[KSumanth]

Hi there, 
I am trying to create records for 35+ days, 60+ days, 90+ days

• In my fact table I have mutilple dates which are duplicate. 
• I have created a Calendar table with condition Calendar (min (date), Max(date)). 
• Next I wanted to create a one more table by using calendar table. I am not able to create a relationship between them.  
  
  1)Calendar table Logic : 

CALENDAR(MIN(tbl[Target Date]),MAX(tbl[Target Date]))

2)Condtional table logic to create a bucket for 35+ days,60+ days, 90+ days records. 

Period Table =

VAR

    _35 = ADDCOLUMNS(

        CALCULATETABLE(

            'Calendar'

            ,DATEDIFF(Calendar[Date],UTCTODAY(),DAY)>35

        )

        ,"Period","35+ days"

    )

VAR

    _60= ADDCOLUMNS(

        CALCULATETABLE(

            'Calendar'

            ,DATEDIFF(Calendar[Date],UTCTODAY(),DAY)>60

        )

        ,"Period","60+ days"

    )

VAR

    _90= ADDCOLUMNS(

        CALCULATETABLE(

            'Calendar'

            ,DATEDIFF(Calendar[Date],UTCTODAY(),DAY)>90

        )

        ,"Period","90+ days"

    )

RETURN UNION(_35,_60,_90)

3) In model View I have created many to one relation with my Fact table and calendar table. 
I wanted to create a relation with Calendar table and Period table, In which PowerBI is throwing an error called circular dependency was detected. 

How can i solve this?  Thanks in advance for your help 

[KSumanth]

Thanks for the response, The provided solution is not working. 

[jgeddes]

You cannot create a relationship between the calendar table and the period table since it appears you used the calendar table to construct the period table. 
You can create a calculated column in your calendar table using a switch statement to create your periods.

It could look something like...

Period =
SWITCH(
TRUE(),
DATEDIFF(Calendar[Date], UTCNOW(), DAY) > 90, "90+ Days",
DATEDIFF(Calendar[Date], UTCNOW(), DAY) >= 60, "60-90 Days",
DATEDIFF(Calendar[Date], UTCNOW(), DAY) >= 35, "35-59 Days",
"Current"
)

[KSumanth]

I understand, Thanks for the response. 
However this is not our business requirement. 
Our Client Business requirement is to show records which are
35+ days ( Include all records which missed target > 35 days)
60+ days ( Include all records which missed target > 60 days)
90+ days ( Include all records which missed target > 90 days)
It means 35+ has more records than 60+ days & 60+ days has more records than 90+ days.

Example: 
2024-03-24 : This record will fall under categorys like 35+  
2024-02-24 : This record will fall under all categorys like 35+ & 60+  
2023-11-24 : This record will fall under all categorys like 35+, 60+, 90+
--------------------------------------------------------------
The similar logic works if days are inbetween, PBI doesnt throw error

Period =
VAR
_Last30days= ADDCOLUMNS(
CALCULATETABLE(
'Calendar'
,DATESBETWEEN('Calendar'[Date],UTCTODAY()-30,UTCTODAY())
)
,"In the last","30 days"
)
VAR
_Last60days= ADDCOLUMNS(
CALCULATETABLE(
'Calendar'
,DATESBETWEEN('Calendar'[Date],UTCTODAY()-60,UTCTODAY())
)
,"In the last","60 days"
)
VAR
_Last90days= ADDCOLUMNS(
CALCULATETABLE(
'Calendar'
,DATESBETWEEN('Calendar'[Date],UTCTODAY()-90,UTCTODAY())
)
,"In the last","90 days"
)
RETURN UNION(_Last30days,_Last60days,_Last90days)

Reference Video: How to create 30/60/90/Lifetime filter in Power BI (youtube.com)

[jgeddes]

Ok, I understand your requirement. 
If you need to create a second date table with the periods you cannot refer to the first calender table. 
Something like this might work for you. 

Table 2 = 
var _vCalendar = CALENDAR(MIN(tbl[Target Date]), MAX(tbl[Target Date]))
var _35 =
ADDCOLUMNS(
    FILTER(_vCalendar, DATEDIFF([Date], UTCTODAY(), DAY) >35),
    "Period", "35+ Days"
)
var _60 =
ADDCOLUMNS(
    FILTER(_vCalendar, DATEDIFF([Date], UTCTODAY(), DAY) >60),
    "Period", "60+ Days"
)
var _90 =
ADDCOLUMNS(
    FILTER(_vCalendar, DATEDIFF([Date], UTCTODAY(), DAY) >90),
    "Period", "90+ Days"
)
RETURN
UNION(_35,_60,_90)

Just mimic the code in your calendar table in the _vCalendar variable. This should allow for the relationship to be built between the two date tables.

[KSumanth]

Thanks for the response,
Here we forgot one more thing, we will be having duplicate records in Calendar table as well. Now I cant have relation for Calendar and my Fact table. 
Even if relation happens Cardinality will be many to many. 

[jgeddes]

My response was not clear. I intend for you to have both a calendar table and a period table proposed in my last response. The period table would filter the calendar table which would then filter your fact table.

[KSumanth]

Thanks for the response, The provided solution is not working.