Forum Discussion
Circular dependency was detected
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"
)
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)
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.
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.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.