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,

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

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