Calculate different end dates.

Hi Matt_JEM ,

The circular dependency error occurs because DAX is attempting to evaluate one column (JEMK04Date) while it depends on calculations within the same table (vw_Rep_Inv_Movements). This happens due to a dependency chain that DAX cannot resolve.

Here’s how you can resolve this issue:

Instead of calculating JEMK04Date in the same table, create a calculated table that pre-computes JEMK03Date and JEMK04Date for each stock code. This breaks the dependency loop.

MovementDatesTable =
ADDCOLUMNS (
    SUMMARIZE (
        'vw_Rep_Inv_Movements',
        'vw_Rep_Inv_Movements'[StockCode],
        'vw_Rep_Inv_Movements'[First Entry Date]
    ),
    "JEMK03Date",
        CALCULATE (
            MIN('vw_Rep_Inv_Movements'[EntryDate]),
            'vw_Rep_Inv_Movements'[NewWarehouse] = "JEMK03"
        ),
    "JEMK04Date",
        CALCULATE (
            MIN('vw_Rep_Inv_Movements'[EntryDate]),
            'vw_Rep_Inv_Movements'[NewWarehouse] = "JEMK04",
            'vw_Rep_Inv_Movements'[Warehouse] = "JEMK03"
        )
)

This creates a separate table (MovementDatesTable) with precomputed values for each stock code.

Modify your measure to refer to the calculated table rather than relying on computed columns in the same table.

Completion Date =
VAR StartDate = 
    CALCULATE (
        MIN('MovementDatesTable'[First Entry Date]),
        'MovementDatesTable'[StockCode] = SELECTEDVALUE('vw_Rep_Inv_Movements'[StockCode])
    )
VAR JEMK04Date =
    CALCULATE (
        MIN('MovementDatesTable'[JEMK04Date]),
        'MovementDatesTable'[StockCode] = SELECTEDVALUE('vw_Rep_Inv_Movements'[StockCode])
    )
VAR CalculationStartDate =
    IF (
        NOT ISBLANK(JEMK04Date),
        JEMK04Date + 1,
        StartDate + 1
    )
VAR DateTable =
    FILTER (
        ADDCOLUMNS (
            CALENDAR (CalculationStartDate, CalculationStartDate + 50),
            "Weekday", WEEKDAY([Date], 2)
        ),
        [Weekday] <= 5
    )
VAR WorkingDaysTable =
    ADDCOLUMNS (
        DateTable,
        "WorkingDayRank", RANKX (DateTable, [Date], , ASC, DENSE)
    )
RETURN
    MINX (
        FILTER (
            WorkingDaysTable,
            [WorkingDayRank] = 20
        ),
        [Date]
    )

Moving the intermediate calculations (JEMK03Date and JEMK04Date) to a separate calculated table removes dependencies within the same table.

DAX can then reference precomputed values without causing a circular dependency.

Best regards,