Circular Dependency was detected for Year-To-Date calculation

[CC852]

I am trying to create YTD Last Year, YTD This Year, and then a YoY calculation/column

So far I have created the following columns:

Max Year = 

max('Sales WeeklySales'[WeekEndDate].[Year])

Max Date =

max('Sales WeeklySales'[WeekEndDate])

YTD TY Sales = 

VAR maxYear = 'Sales WeeklySales'[Max Year]

VAR YTDTYStartDate = DATE(maxYear, 1, 1)

VAR YTDTYEndDate = MAX('Sales WeeklySales'[Max Date])

VAR YTDSales =

    CALCULATE(

    SUM('Sales WeeklySales'[Validations]),

        DATESBETWEEN('Sales WeeklySales'[WeekEndDate], YTDTYStartDate, YTDTYEndDate)

    )

RETURN

    YTDSales

YTD LY Start Date = 

VAR lastYear = max('Sales WeeklySales'[Max Year])  - 1

VAR YTDLYStartDate = DATE(lastYear,1,1)

 

RETURN

   YTDLYStartDate

YTD LY End Date = 

VAR lastYear = max('Sales WeeklySales'[Max Year])  - 1

VAR YTDLYStartDate = DATE(lastYear,1,1)
 
VAR maxDateTY = MAX('Sales WeeklySales'[Max Date])

VAR monthDate = MONTH(maxDateTY)

VAR dayDate = DAY(maxDateTY)

VAR YTDLYEndDate = DATE(lastYear, monthDate, dayDate)

RETURN

   YTDLYEndDate

 I am then getting an error when I come to create the following:

A circular dependency was detected: Sales WeeklySales[YTD LY Sales], Sales WeeklySales[YTD TY Sales], Sales WeeklySales[YTD LY Sales].

YTD LY Sales =

    CALCULATE(

        SUM('Sales WeeklySales'[Validations]),

        DATESBETWEEN( 'Sales WeeklySales'[WeekEndDate], 'Sales WeeklySales'[YTD LY Start Date], 'Sales WeeklySales'[YTD LY End Date])

    )

 Where is the circular dependency?

Thanks in advance!

[sevenhills]

Can you try this first?

YTD TY Sales = 

-- Assuming WeekEndDate is a date column
var _maxDt = CALCULATE( MAX('Sales WeeklySales'[WeekEndDate]), ALLSELECTED('Sales WeeklySales')) 
 
var _YTD_Sales =  CALCULATE( 
   			SUM('Sales WeeklySales'[Validations]), 
        		'Sales WeeklySales'[WeekEndDate] >= DATE( YEAR(_maxDt ), 1, 1)
				&& 'Sales WeeklySales'[WeekEndDate] <= _maxDt )
                  )
RETURN
    _YTD_Sales 

Will it works?

Now, let us try this next?

LYTD TY Sales = 
-- Assuming the above YTD TY Sales working! 

-- Assuming WeekEndDate is a date column
var _maxDt = CALCULATE( MAX('Sales WeeklySales'[WeekEndDate]), ALLSELECTED('Sales WeeklySales'))  

-- It will not work if it is a leap year, so adjust for this day
var _LY_maxDt = if ( Month(_maxDt) = 2 && Day(_maxDt) = 29, 
				DATE( YEAR(_maxDt ) - 1, MONTH(_maxDt), 28 ),
				 DATE( YEAR(_maxDt ) - 1, MONTH(_maxDt), DAY(_maxDt) )
			)

var _LYTD_Sales =  CALCULATE( 
   			SUM('Sales WeeklySales'[Validations]), 
        		'Sales WeeklySales'[WeekEndDate] >= DATE( YEAR(_maxDt) - 1, 1, 1)
				&& 'Sales WeeklySales'[WeekEndDate] <= _LY_maxDt 
                  )
RETURN
    _LYTD_Sales 

[CC852]

@sevenhills thank you for the reply!

YTD TY Sales formula worked but then when it came to YTD LY Sales, I got the same error message 😞

"A circular dependency was detected"

[sevenhills]

Interesting ... the YTD TY Sales working and not the previous year.

To fix a circular dependency, you need to find the source of the circular reference and either remove it or redesign the calculation so that it does not depend on itself. This is what I did. 

Could you do one step a time, remove all variables and start adding one after and another and let me know where exactly the circular dependency is coming.

LYTD TY Sales = 
-- Assuming the above YTD TY Sales working! 

-- Assuming WeekEndDate is a date column
var _maxDt = CALCULATE( MAX('Sales WeeklySales'[WeekEndDate]), ALLSELECTED('Sales WeeklySales'))  

-- It will not work if it is a leap year, so adjust for this day
var _LY_maxDt = if ( Month(_maxDt) = 2 && Day(_maxDt) = 29, 
				DATE( YEAR(_maxDt ) - 1, MONTH(_maxDt), 28 ),
				 DATE( YEAR(_maxDt ) - 1, MONTH(_maxDt), DAY(_maxDt) )
			)

var _LYTD_Sales =  CALCULATE( 
   			SUM('Sales WeeklySales'[Validations]), 
        		'Sales WeeklySales'[WeekEndDate] >= DATE( YEAR(_maxDt) - 1, 1, 1)
				&& 'Sales WeeklySales'[WeekEndDate] <= _LY_maxDt 
                  )
RETURN
    _LYTD_Sales 

Try this as starting point.

LYTD TY Sales = 
-- Assuming the above YTD TY Sales working! 

-- Assuming WeekEndDate is a date column
var _maxDt = CALCULATE( MAX('Sales WeeklySales'[WeekEndDate]), ALLSELECTED('Sales WeeklySales'))  
 
RETURN
    _maxDt 

Check whether you are getting the date value correctly for the max date!

next with this 

LYTD TY Sales = 
-- Assuming the above YTD TY Sales working! 

-- Assuming WeekEndDate is a date column
var _maxDt = CALCULATE( MAX('Sales WeeklySales'[WeekEndDate]), ALLSELECTED('Sales WeeklySales'))  

-- It will not work if it is a leap year, so adjust for this day
var _LY_maxDt = if ( Month(_maxDt) = 2 && Day(_maxDt) = 29, 
				DATE( YEAR(_maxDt ) - 1, MONTH(_maxDt), 28 ),
				 DATE( YEAR(_maxDt ) - 1, MONTH(_maxDt), DAY(_maxDt) )
			)
 
RETURN
    _LY_maxDt 

Check whether you are getting the date value correctly for the last year max date!

and next with this

LYTD TY Sales = 
-- Assuming the above YTD TY Sales working! 

-- Assuming WeekEndDate is a date column
var _maxDt = CALCULATE( MAX('Sales WeeklySales'[WeekEndDate]), ALLSELECTED('Sales WeeklySales'))  

-- It will not work if it is a leap year, so adjust for this day
var _LY_maxDt = if ( Month(_maxDt) = 2 && Day(_maxDt) = 29, 
				DATE( YEAR(_maxDt ) - 1, MONTH(_maxDt), 28 ),
				 DATE( YEAR(_maxDt ) - 1, MONTH(_maxDt), DAY(_maxDt) )
			)

var _LYTD_Sales =  CALCULATE( 
   			SUM('Sales WeeklySales'[Validations]), 
        		'Sales WeeklySales'[WeekEndDate] >= DATE( 2022, 1, 1)
				&& 'Sales WeeklySales'[WeekEndDate] <= DATE( 2023, 12, 31)
                  )
RETURN
    _LYTD_Sales 

hard code the date values and see as no errors and returning the data!

and next substitute 

DATE( 2022, 1,1) as _maxDT and see if it works

and try with _LY_maxDt ...

Let me know at what point you get the circular dependency. (I forgot to ask, is Validations is a column or a measure? that is when it happens if the measure has same referenes, which I am aware of it! )

[CC852]

Thanks for breaking it down for me @sevenhills 

To clarify your question at the end: Validations is a column.

The DAX formula works for RETURN _max_DT:

YTD LY Sales = 

-- Assuming the above YTD TY Sales working! 

-- Assuming WeekEndDate is a date column

var _maxDt = CALCULATE( MAX('Sales WeeklySales'[WeekEndDate]), ALLSELECTED('Sales WeeklySales'))  

RETURN

    _maxDt

It then works for RETURN _LY_maxDT:

YTD LY Sales =

-- Assuming the above YTD TY Sales working! 

-- Assuming WeekEndDate is a date column

var _maxDt = CALCULATE( MAX('Sales WeeklySales'[WeekEndDate]), ALLSELECTED('Sales WeeklySales'))  

-- It will not work if it is a leap year, so adjust for this day

var _LY_maxDt = if ( Month(_maxDt) = 2 && Day(_maxDt) = 29, 

                DATE( YEAR(_maxDt ) - 1, MONTH(_maxDt), 28 ),

                 DATE( YEAR(_maxDt ) - 1, MONTH(_maxDt), DAY(_maxDt) )

            )

 

RETURN

    _LY_maxDt

However, it stops working when the CALCULATE function is added:

YTD LY Sales =

-- Assuming the above YTD TY Sales working! 

-- Assuming WeekEndDate is a date column

var _maxDt = CALCULATE( MAX('Sales WeeklySales'[WeekEndDate]), ALLSELECTED('Sales WeeklySales'))  

-- It will not work if it is a leap year, so adjust for this day

var _LY_maxDt = if ( Month(_maxDt) = 2 && Day(_maxDt) = 29, 

                DATE( YEAR(_maxDt ) - 1, MONTH(_maxDt), 28 ),

                 DATE( YEAR(_maxDt ) - 1, MONTH(_maxDt), DAY(_maxDt) )

            )

 

var _LYTD_Sales =  CALCULATE( 

            SUM('Sales WeeklySales'[Validations]), 

                'Sales WeeklySales'[WeekEndDate] >= DATE( 2022, 1, 1)

                && 'Sales WeeklySales'[WeekEndDate] <= DATE( 2023, 12, 31)

                  )

RETURN

    _LYTD_Sales

[sevenhills]

One more try and see if it works.

YTD TY Sales and LYTD TY Sales are the two measures. 

Let us say, YTD TY Sales is working. Remove this measure (temporary).

Do the LYTD TY Sales, it should be working! IF this is true, I guess, these articles apply for your situation:

For more details about circular dependencies, please refer:

1. Understanding-circular-dependencies 
2. Avoiding-circular-dependency-errors-in-dax 
3. https://www.youtube.com/watch?v=m-qhcDcNKrE  

[CC852]

@sevenhills - you're right, removing my column 'YTD TY Sales' removes all errors.

I will have a look at those resources you gave me, thank you for your help!!

[Greg_Deckler]

@CC852 One of the many, many reasons I have abandoned CALCULATE and DAX TI functions. You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

Also:

[CC852]

@Greg_Deckler thank you for all the information.

As a novice PBI user, which article should I go through to guide me through my issue?

[CC852]

@Greg_Deckler 
I found your article about DATESBETWEEN and I used the DAX formula you wrote but I adapted it to what I needed.  https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/To-bleep-With-DATESBETWEEN/td-p/125...

ToHellWithDATESBETWEEN = 
    VAR __StartDate = MINX(ALL(ProductInventory[Date]),[Date])  //could be anything, this emulates BLANK()
    VAR __EndDate = MAX(ProductInventory[Date])  //could be anything
    VAR __DATESBETWEEN = 
        DISTINCT(
            FILTER(
                SELECTCOLUMNS(
                    ALL('ProductInventory'),
                    "Date",[Date]
                ),
                [Date] >= __StartDate && [Date] <= __EndDate
            )
        )
RETURN
    SUMX(FILTER(ALL('ProductInventory'),[Date] IN __DATESBETWEEN),'ProductInventory'[UnitCost]*'ProductInventory'[UnitsBalance])

So my columns involved are: 

Max Year = 

max('Sales WeeklySales'[WeekEndDate].[Year])

Max Date =

max('Sales WeeklySales'[WeekEndDate])

YTD LY Start Date = 

VAR lastYear = max('Sales WeeklySales'[Max Year])  - 1

VAR YTDLYStartDate = DATE(lastYear,1,1)

 

RETURN

   YTDLYStartDate

YTD LY End Date = 

VAR lastYear = max('Sales WeeklySales'[Max Year])  - 1

VAR YTDLYStartDate = DATE(lastYear,1,1)
 
VAR maxDateTY = MAX('Sales WeeklySales'[Max Date])

VAR monthDate = MONTH(maxDateTY)

VAR dayDate = DAY(maxDateTY)

VAR YTDLYEndDate = DATE(lastYear, monthDate, dayDate)

RETURN

   YTDLYEndDate

And here I've used your DAX formula but adapted to make it work with my data:

YTD LY Sales ALT = 
VAR __StartDate = 'Sales WeeklySales'[YTD LY Start Date]
VAR __EndDate = 'Sales WeeklySales'[YTD LY End Date]
VAR __DATESBETWEEN = 

DISTINCT(
FILTER(
SELECTCOLUMNS(
ALL('Sales WeeklySales'[Validations]),
'Sales WeeklySales'[WeekEndDate]),
'Sales WeeklySales'[WeekEndDate] >= __StartDate && 'Sales WeeklySales'[WeekEndDate] <= __EndDate))

RETURN
SUMX(FILTER(ALL('Sales WeeklySales'[Validations]), 'Sales WeeklySales'[WeekEndDate]IN __DATESBETWEEN))

Error: Too few arguments were passed to the SUMX function. The minimum argument count for the function is 2.

I'm getting an error for the SUMX and I don't know how to work around it as I'm looking to get the SUM of Validations between Start Date and End Date 

Please could you let me know if you can help me on this?

[Greg_Deckler]

@CC852 So you need to put a column or measure in the place indicated below:

SUMX(FILTER(ALL('Sales WeeklySales'[Validations]), 'Sales WeeklySales'[WeekEndDate]IN __DATESBETWEEN), [You need something here] )

[CC852]

Hi @Greg_Deckler - hoping you see this and can help me with the DAX you suggested above

Thank you!