Forum Discussion

Matt_JEM's avatar
Matt_JEM
Helper I
1 year ago
Solved

Calculate different end dates.

Good day All.   I have the following code that adds 20 working days to the stockcode "First Entry Date"and this work very well. I need help to add the following condition to the code.   Once the ...
Need Help
  • DataNinja777's avatar
    DataNinja777
    1 year ago

    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,

shafiz_p's avatar
shafiz_p
Super User
1 year ago

Hi Matt_JEM  Without representative data it is hard to tell specific solution. But I think, you need to capture date when stock moved from JEMK03 to JEMK04 and redefine EffectiveDate to check if date is blank of stock moved from JEMK03 to JEMK04, then use First Entry Date other wise the day it is moved. For example, you could try this to adjust your code:

--Replace 'vw_Rep_Inv_Movements'[Date] with column name which capture moving date from JEMK03 to JEMK04.

VAR JEMK04Date = 
    CALCULATE(
        MIN('vw_Rep_Inv_Movements'[Date]),
        'vw_Rep_Inv_Movements'[NewWarehouse] = "JEMK04"
    )
VAR EffectiveStartDate = 
    IF(
        ISBLANK(JEMK04Date),
        StartDate,
        JEMK04Date
    )

 

 

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

 

Best Regards,
Shahariar Hafiz