topic Re: Circular Dependency was detected for Year-To-Date calculation in DAX Commands and Tips

Circular Dependency was detected for Year-To-Date calculation

CC852 — Thu, 29 Feb 2024 14:45:41 GMT

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!

Re: Circular Dependency was detected for Year-To-Date calculation

Greg_Deckler — Thu, 29 Feb 2024 14:54:48 GMT

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-TITHW/m-p/434008

Also:

Re: Circular Dependency was detected for Year-To-Date calculation

sevenhills — Fri, 01 Mar 2024 21:23:24 GMT

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

Re: Circular Dependency was detected for Year-To-Date calculation

CC852 — Fri, 01 Mar 2024 10:26:43 GMT

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"

Re: Circular Dependency was detected for Year-To-Date calculation

CC852 — Fri, 01 Mar 2024 10:33:32 GMT

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?

Re: Circular Dependency was detected for Year-To-Date calculation

sevenhills — Fri, 01 Mar 2024 21:32:14 GMT

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.

Try this as starting point.

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

next with this

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! )

Re: Circular Dependency was detected for Year-To-Date calculation

CC852 — Tue, 05 Mar 2024 14:10:40 GMT

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

Re: Circular Dependency was detected for Year-To-Date calculation

sevenhills — Tue, 05 Mar 2024 23:55:19 GMT

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:

Re: Circular Dependency was detected for Year-To-Date calculation

CC852 — Wed, 06 Mar 2024 15:56:57 GMT

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!!

Re: Circular Dependency was detected for Year-To-Date calculation

CC852 — Wed, 06 Mar 2024 17:13:33 GMT

@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/1252805

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?

Re: Circular Dependency was detected for Year-To-Date calculation

CC852 — Tue, 19 Mar 2024 13:54:40 GMT

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

Thank you!

Re: Circular Dependency was detected for Year-To-Date calculation

Greg_Deckler — Tue, 19 Mar 2024 16:29:19 GMT

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] )