Calculate different end dates.

[Matt_JEM]

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 stock code move into 'vw_Rep_Inv_Movements'[NewWarehouse] = "JEMK03" there is a shortage of components to complete the project. Only once the last component is received will the stock code move to  'vw_Rep_Inv_Movements'[NewWarehouse] = "JEMK04". 

What I want to achieve is that if the stock codes does not move into JEMK03 the completion date must be calculated from "First Entry Date as below, but if the stock code moves to JEMK03 the completion date must be calculated from the date that the stock code moved from JEMK03 to JEMK04.

Completion Date =

VAR StartDate = 'vw_Rep_Inv_Movements'[First Entry Date]

VAR DaysToAdd = 20

VAR DateTable =

    FILTER (

        ADDCOLUMNS (

            CALENDAR (StartDate, StartDate + DaysToAdd + 30),  -- Create a date range

            "Weekday", WEEKDAY([Date], 2)  -- 2 means Monday=1, Sunday=7

        ),

        [Weekday] <= 5  -- Exclude weekends

    )

VAR WorkingDaysTable =

    ADDCOLUMNS (

        DateTable,

        "WorkingDayRank", RANKX ( DateTable, [Date], , ASC, DENSE )

    )

RETURN

    MINX (

        FILTER (

            WorkingDaysTable,

            [WorkingDayRank] = DaysToAdd

        ),

        [Date]

    )

I thank you all in advance for your time and assistance

[Kedar_Pande]

@Matt_JEM 

Updated DAX Code:

Completion Date =
VAR StartDate = 'vw_Rep_Inv_Movements'[First Entry Date]
VAR DaysToAdd = 20

-- Check if the stock code moved into JEMK03
VAR JEMK03Date = 
MINX(
FILTER(
'vw_Rep_Inv_Movements',
'vw_Rep_Inv_Movements'[NewWarehouse] = "JEMK03"
),
'vw_Rep_Inv_Movements'[Movement Date] -- Replace with the column that tracks movement dates
)

-- Check if the stock code moved into JEMK04 after JEMK03
VAR JEMK04Date = 
MINX(
FILTER(
'vw_Rep_Inv_Movements',
'vw_Rep_Inv_Movements'[NewWarehouse] = "JEMK04" &&
'vw_Rep_Inv_Movements'[Movement Date] > JEMK03Date
),
'vw_Rep_Inv_Movements'[Movement Date]
)

-- Determine the adjusted start date
VAR AdjustedStartDate = 
IF(
NOT ISBLANK(JEMK03Date) && NOT ISBLANK(JEMK04Date),
JEMK04Date,
StartDate
)

-- Create a table of working days
VAR DateTable =
FILTER (
ADDCOLUMNS (
CALENDAR (AdjustedStartDate, AdjustedStartDate + DaysToAdd + 30), -- Create a date range
"Weekday", WEEKDAY([Date], 2) -- 2 means Monday=1, Sunday=7
),
[Weekday] <= 5 -- Exclude weekends
)

-- Rank the working days
VAR WorkingDaysTable =
ADDCOLUMNS (
DateTable,
"WorkingDayRank", RANKX ( DateTable, [Date], , ASC, DENSE )
)

-- Return the calculated completion date
RETURN
MINX (
FILTER (
WorkingDaysTable,
[WorkingDayRank] = DaysToAdd
),
[Date]
)

Let me know if you need further clarification!

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[shafiz_p]

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

[DataNinja777]

Hi @Matt_JEM ,

Here’s how you can modify your DAX measure to include the logic for determining the completion date based on whether the stock code moves to JEMK03 and subsequently to JEMK04:

Completion Date =
VAR StartDate = 'vw_Rep_Inv_Movements'[First Entry Date]
VAR DaysToAdd = 20

-- Find the date when the stock code moved to JEMK03
VAR JEMK03Date = 
    CALCULATE (
        MIN('vw_Rep_Inv_Movements'[Movement Date]), 
        'vw_Rep_Inv_Movements'[NewWarehouse] = "JEMK03"
    )

-- Find the date when the stock code moved to JEMK04 after JEMK03
VAR JEMK04Date =
    CALCULATE (
        MIN('vw_Rep_Inv_Movements'[Movement Date]),
        'vw_Rep_Inv_Movements'[NewWarehouse] = "JEMK04",
        'vw_Rep_Inv_Movements'[Movement Date] > JEMK03Date
    )

-- Determine the calculation start date based on the conditions
VAR CalculationStartDate =
    IF (
        NOT ISBLANK(JEMK03Date) && NOT ISBLANK(JEMK04Date),
        JEMK04Date, -- Use the date stock moved to JEMK04 if it passed through JEMK03
        StartDate   -- Otherwise, use the First Entry Date
    )

-- Create a table of dates considering only working days
VAR DateTable =
    FILTER (
        ADDCOLUMNS (
            CALENDAR (CalculationStartDate, CalculationStartDate + DaysToAdd + 30),  -- Create a date range
            "Weekday", WEEKDAY([Date], 2)  -- 2 means Monday=1, Sunday=7
        ),
        [Weekday] <= 5  -- Exclude weekends
    )

-- Rank the working days
VAR WorkingDaysTable =
    ADDCOLUMNS (
        DateTable,
        "WorkingDayRank", RANKX ( DateTable, [Date], , ASC, DENSE )
    )

-- Return the calculated completion date
RETURN
    MINX (
        FILTER (
            WorkingDaysTable,
            [WorkingDayRank] = DaysToAdd
        ),
        [Date]
    )

1. JEMK03Date and JEMK04Date: These variables calculate the respective movement dates for JEMK03 and JEMK04.
• JEMK03Date finds the first date when the stock code moved to JEMK03.
• JEMK04Date finds the first date after JEMK03Date when the stock code moved to JEMK04.
2. CalculationStartDate: Determines the starting date for the calculation:
• If the stock code moved through both JEMK03 and JEMK04, the calculation starts from JEMK04Date.
• Otherwise, it starts from the "First Entry Date."
3. Completion Date Logic: The rest of the code remains the same, calculating the 20th working day from the selected start date.

Use Case:

• If the stock code does not move to JEMK03, the calculation starts from the "First Entry Date."
• If the stock code moves to JEMK03 and subsequently to JEMK04, the calculation starts from JEMK04Date.

This formula ensures that the completion date reflects the conditional logic you specified.

Best regards,

[Matt_JEM]

@DataNinja777 

I have implemented the code below and keep getting the following error. Do you have any suggestions on how to resolve this? I thank you in advance for your time.
"A circular dependency was detected: vw_Rep_Inv_Movements[JEMK04Date], vw_Rep_Inv_Movements[Column], vw_Rep_Inv_Movements[JEMK04Date]."

I determine JEMK04Date =

    CALCULATE (

        MIN('vw_Rep_Inv_Movements'[EntryDate]),

        'vw_Rep_Inv_Movements'[NewWarehouse] = "JEMK04",

        'vw_Rep_Inv_Movements'[Warehouse] = "JEMK03"

    )

Then I get the following result . The JEMK04DATE is the correct date when the stock code have moved from JEMK03 to JEMK04. My expected completion date is the 11 NOv 2024

First Entry Date	JEMK04Date	StockCode
2024-10-09 00:00:00		F00020-INC001-A01-L00-T00
2024-10-09 00:00:00	2024-10-14 00:00:00	F00020-INC001-A01-L00-T00
2024-10-09 00:00:00		F00020-INC001-A01-L00-T07
2024-10-09 00:00:00	2024-10-14 00:00:00	F00020-INC001-A01-L00-T07

Completion Date =

VAR StartDate = 'vw_Rep_Inv_Movements'[First Entry Date]

VAR DaysToAdd = 20

-- Determine the calculation start date based on the conditions

VAR CalculationStartDate =

    IF (

        NOT ISBLANK('vw_Rep_Inv_Movements'[JEMK04Date]),

        'vw_Rep_Inv_Movements'[JEMK04Date] + 1, -- Use the date stock moved to JEMK04 and start from the next day

        StartDate + 1   -- Otherwise, use the First Entry Date and start from the next day

    )

-- Create a table of dates considering only working days

VAR DateTable =

    FILTER (

        ADDCOLUMNS (

            CALENDAR (CalculationStartDate, CalculationStartDate + DaysToAdd + 30),  -- Create a date range

            "Weekday", WEEKDAY([Date], 2)  -- 2 means Monday=1, Sunday=7

        ),

        [Weekday] <= 5  -- Exclude weekends

    )

-- Rank the working days

VAR WorkingDaysTable =

    ADDCOLUMNS (

        DateTable,

        "WorkingDayRank", RANKX ( DateTable, [Date], , ASC, DENSE )

    )

-- Return the calculated completion date

RETURN

    MINX (

        FILTER (

            WorkingDaysTable,

            [WorkingDayRank] = DaysToAdd

        ),

        [Date]

    )

[DataNinja777 · Accepted solution]

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,