Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
WilliamA
Frequent Visitor

Circular dependency detected all of a sudden with no changes made.

Hi all,

 

I am using a DAX table to create a date selector containing this month, last month etc. recently we have gotten this error message: 

"A circular dependency was detected: Date Selection[Date Selection], 485ca740-1d0d-4924-b39b-1c186ee25053, Distinct Dates[Date], Distinct Dates[Distinct Dates], Date Selection[Date], Date Selection[Date Selection]."

 

this error is a result of this variable:

VAR __MonthStart = CALCULATE(STARTOFMONTH('Date'[Date]),YEAR('Date'[Date]) = YEAR(__TodaysDate),MONTH('Date'[Date]) = MONTH(__TodaysDate))
the full dax is below - it runs fine at the moment since the monthly selections are commented out. I have also provided a screenshot of the model view for reference. relationships from the dates table go to the dim tables.
 
Is there a reason why this is happening all of a sudden? I've tried rewriting the dax with no luck.
WilliamA_0-1732754688953.png
 
Date Selection =
VAR __FY = 07
VAR __TodaysDate = TODAY()
VAR __CYearStart  = CALCULATE(STARTOFYEAR('Date'[Date]),YEAR('Date'[Date]) = YEAR(__TodaysDate))
VAR __CYearEnd  = CALCULATE(ENDOFYEAR('Date'[Date]),YEAR('Date'[Date]) = YEAR(__TodaysDate))
VAR __FYearStart  = IF(DATE(YEAR(TODAY()), VALUE(__FY), 1) <= TODAY(), DATE(YEAR(TODAY()), VALUE(__FY), 1), DATE(YEAR(TODAY()) - 1, VALUE(__FY), 1))
VAR __MonthStart = CALCULATE(STARTOFMONTH('Date'[Date]),YEAR('Date'[Date]) = YEAR(__TodaysDate),MONTH('Date'[Date]) = MONTH(__TodaysDate))
VAR __MonthEnd = CALCULATE(ENDOFMONTH('Date'[Date]),YEAR('Date'[Date]) = YEAR(__TodaysDate),MONTH('Date'[Date]) = MONTH(__TodaysDate))

--Last Year
VAR __SameDayLY = DATE(YEAR(TODAY()) - 1, MONTH(TODAY()), DAY(TODAY()))
VAR __MonthStartLY = DATE(YEAR(__MonthStart) - 1, MONTH(__MonthStart), DAY(__MonthStart))
VAR __MonthEndLY = DATE(YEAR(__MonthEnd) - 1, MONTH(__MonthEnd), DAY(__MonthEnd))
VAR __QuarterStartLY = CALCULATE(STARTOFQUARTER('Date'[Date]),YEAR('Date'[Date]) = YEAR(__TodaysDate) - 1, quarter('Date'[Date]) = quarter(__TodaysDate))
VAR __QuarterEndLY = CALCULATE(ENDOFQUARTER('Date'[Date]),YEAR('Date'[Date]) = YEAR(__TodaysDate) - 1, quarter('Date'[Date]) = quarter(__TodaysDate))
VAR __LYearStart  = CALCULATE(STARTOFYEAR('Date'[Date]),YEAR('Date'[Date]) = YEAR(__TodaysDate) - 1)
VAR __LYearEnd  = CALCULATE(ENDOFYEAR('Date'[Date]),YEAR('Date'[Date]) = YEAR(__TodaysDate) - 1)
----------------------------
VAR __Last1Month = CALCULATE(
    STARTOFMONTH('Date'[Date]),
    FILTER('Date', 'Date'[Date] >= EDATE(TODAY(),-1))
)

VAR __Last6Months = CALCULATE(
    STARTOFMONTH('Date'[Date]),
    FILTER('Date', 'Date'[Date] >= EDATE(TODAY(),-6))
)
VAR __Last12Months = CALCULATE(
    STARTOFMONTH('Date'[Date]),
    FILTER('Date', 'Date'[Date] >= EDATE(TODAY(),-12) )
)
VAR __Last24Months = CALCULATE(
    STARTOFMONTH('Date'[Date]),
    FILTER('Date', 'Date'[Date] >= EDATE(TODAY(),-24) )
)

var __EarliestDate = MIN('Date'[Date])
VAR __QuarterStart = CALCULATE(STARTOFQUARTER('Date'[Date]),YEAR('Date'[Date]) = YEAR(__TodaysDate),quarter('Date'[Date]) = quarter(__TodaysDate))
VAR __QuarterEnd= CALCULATE(ENDOFQUARTER('Date'[Date]),YEAR('Date'[Date]) = YEAR(__TodaysDate),quarter('Date'[Date]) = quarter(__TodaysDate))
VAR __LastQuarterStart =
    CALCULATE(
        STARTOFQUARTER('Date'[Date]),
        YEAR('Date'[Date]) = YEAR(TODAY()) - IF(QUARTER(TODAY()) = 1, 1, 0),
        QUARTER('Date'[Date]) = IF(QUARTER(TODAY()) = 1, 4, QUARTER(TODAY()) - 1)
    )
VAR __LastQuarterEnd =
    CALCULATE(
        ENDOFQUARTER('Date'[Date]),
        YEAR('Date'[Date]) = YEAR(TODAY()) - IF(QUARTER(TODAY()) = 1, 1, 0),
        QUARTER('Date'[Date]) = IF(QUARTER(TODAY()) = 1, 4, QUARTER(TODAY()) - 1)
    )

VAR __LastMonthStart =
    CALCULATE(
        STARTOFMONTH('Date'[Date]),
        YEAR('Date'[Date]) = YEAR(TODAY()) - IF(MONTH(TODAY()) = 1, 1, 0),
        MONTH('Date'[Date]) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1)
    )
VAR __LastMonthEnd =
    CALCULATE(
        ENDOFMONTH('Date'[Date]),
        YEAR('Date'[Date]) = YEAR(TODAY()) - IF(MONTH(TODAY()) = 1, 1, 0),
        MONTH('Date'[Date]) = IF(MONTH(TODAY()) = 1, 12, MONTH(TODAY()) - 1)
    )



VAR RESULT =

UNION(
    ADDCOLUMNS(CALENDAR(__EarliestDate,__TodaysDate),"Selection","All","Order",1),
    ADDCOLUMNS(CALENDAR(__FYearStart,__TodaysDate),"Selection","Financial Year to Date","Order",3),
    ADDCOLUMNS(CALENDAR(__MONTHSTART,__TodaysDate),"Selection","Month to Date","Order",4),
    ADDCOLUMNS(CALENDAR(__CYearStart,__TodaysDate),"Selection","Year to Date","Order",5),
    ADDCOLUMNS(CALENDAR(__TodaysDate - 365,__TodaysDate),"Selection","Last 12 Months","Order",7),
    ADDCOLUMNS(CALENDAR(__TodaysDate - 730,__TodaysDate),"Selection","Last 24 Months","Order",8),
    ADDCOLUMNS(CALENDAR(__TodaysDate - 30,__TodaysDate),"Selection","Last 30 days","Order",9),
    ADDCOLUMNS(CALENDAR(__TodaysDate - 60,__TodaysDate),"Selection","Last 60 days","Order",10),
    ADDCOLUMNS(CALENDAR(__TodaysDate - 90,__TodaysDate),"Selection","Last 90 days","Order",11),
    ADDCOLUMNS(CALENDAR(__MONTHSTART,__MonthEnd),"Selection","Current Month","Order",13),
    ADDCOLUMNS(CALENDAR(__CYearStart,__CYearEnd),"Selection","Current Year","Order",14),
    ADDCOLUMNS(CALENDAR(__LastMonthStart,__LastMonthEnd),"Selection","Previous Month","Order",15),
    ADDCOLUMNS(CALENDAR(__LYearStart,__LYearEnd),"Selection","Previous Year","Order",16),
    ADDCOLUMNS(CALENDAR(__TodaysDate,__TodaysDate),"Selection","Today","Order",17),
    ADDCOLUMNS(CALENDAR(__TodaysDate - 1,__TodaysDate - 1),"Selection","Yesterday","Order",18),
    ADDCOLUMNS(CALENDAR(__TodaysDate - 7,__TodaysDate),"Selection","Last Week","Order",19)
 

 )
RETURN RESULT
3 REPLIES 3
v-stephen-msft
Community Support
Community Support

Hi @WilliamA ,

 

Thanks for reaching out to our community.

Here are some steps to help you troubleshoot and resolve this issue:

You could break down your DAX code into smaller parts and test each part separately. In short, you can change the returned result to a different var in turn. This can help you identify the specific part of the code that is causing the circular dependency.

If there is no problem with the above.

Then ensure that the variables you are using do not depend on each other in a way that creates a loop. For example, make sure that __MonthStart is not indirectly referring back to itself through other variables. 

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

danextian
Super User
Super User

Hi @WilliamA 

 

Are you using  CALENDARAUTO() to create your dates table? If so, this is a known bug. I would suggest you use CALENDAR instead and specify the start and end dates.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Hi @danextian, it is using normal calander function not calander auto

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.