Calculate different end dates.

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,