Changing dates in based on common values and requirements

Hi Anonymous ,
Let's see, you’re managing milestone scheduling logic for courses, where each course has 7 key milestones, and date logic must dynamically adjust based on:

1. Weekends

2. Public Holidays (UK – England and Wales)

3. Off-period logic (2nd week of Dec to 1st Jan)

4. New lead times after 05 Aug 2025

🔍 Objective

You want to ensure only milestones after 05 Aug 2025 are adjusted:

• Skip weekends and holidays.

• Move dates in the off period to a fixed working day.

• Cascade adjustments based on Course Published or Course Built milestones.
  
  

✅ Steps I would take

Step 1: Dates Table – Public Holidays & Weekends

You’ve already done this well. Just verify:

• IsHoliday = TRUE/FALSE from the UK Bank Holidays API.

• IsWeekend = TRUE if WEEKDAY([Date], 2) is 6 (Saturday) or 7 (Sunday).

• Merge this into your Milestones table via the Due Date.
  
  

Step 2: Mark Future Milestones (Post 05 Aug 2025)

In Power Query (good choice):

IsFutureDate = [Due Date] >= #date(2025, 8, 5)

✅ Use this for filtering where updates are needed.

Step 3: Adjust for Weekends & Holidays

Your AdjustedDueDate column is mostly correct, but it subtracts 1 extra day when moving holidays. We’ll fix this by:

• Only adjusting once.

• Moving to the previous working day (not one more).

🔁 Replace your DAX with this:

AdjustedDueDate =
VAR OriginalDate = Milestones[Due Date]
VAR IsFuture = Milestones[IsFutureDate]
VAR WeekdayNum = WEEKDAY(OriginalDate, 2)
VAR IsWeekend = WeekdayNum IN {6, 7}
VAR HolidayTable = FILTER(ALL(Dates), Dates[IsHoliday] = TRUE)
VAR IsHoliday = Milestones[IsHoliday]
VAR WorkingDate =
    IF(
        IsFuture,
        // Loop back to the nearest working day
        CALCULATE(
            MAX(Dates[Date]),
            FILTER(
                ALL(Dates),
                Dates[Date] <= OriginalDate &&
                Dates[IsHoliday] = FALSE &&
                Dates[IsWeekend] = FALSE
            )
        ),
        OriginalDate
    )
RETURN
    WorkingDate

💡 Explanation: This avoids double-subtracting (Sat→Fri then -1 again). It pulls the latest date that’s not a weekend or holiday.

Step 4: Create IsOffPeriod Column

You’re close here. Just a small improvement to make logic a bit more readable.

Replace your DAX with:

IsOffPeriod =
VAR AdjDate = Milestones[AdjustedDueDate]
VAR YearToCheck = YEAR(AdjDate)
VAR DecStart = DATE(YearToCheck, 12, 8 )  // 2nd Monday of December
VAR DecWeekStart =
    DecStart - WEEKDAY(DecStart, 2) + 1  // Get Monday of that week
VAR EndDate = DATE(YearToCheck + 1, 1, 1)  // Jan 1

RETURN
    IF(
        Milestones[IsFutureDate] &&
        AdjDate >= DecWeekStart &&
        AdjDate <= EndDate,
        TRUE,
        FALSE
    )

Step 5: Move Dates That Fall in Off Period

For any milestone where IsOffPeriod = TRUE, we’ll:

• Move to last working day of the first week of December.

Let’s calculate this as a new column:

FinalDueDate =
VAR BaseDate = Milestones[AdjustedDueDate]
VAR IsOff = Milestones[IsOffPeriod]
VAR Year = YEAR(BaseDate)
VAR DecFirst = DATE(Year, 12, 1)
VAR FirstFriday =
    CALCULATE(
        MAX(Dates[Date]),
        FILTER(
            ALL(Dates),
            Dates[Date] >= DecFirst &&
            Dates[Date] <= DecFirst + 6 &&
            Dates[IsWeekend] = FALSE &&
            Dates[IsHoliday] = FALSE
        )
    )
RETURN
    IF(IsOff, FirstFriday, BaseDate)

Step 6: Cascade Changes If “Course Published” or “Course Built” Is Updated

Let’s break this into a measure-based logic or calculated column.

You need to:

• Locate Course Published

• Back-calculate related milestones for the same course.

Here’s how to implement this:

1. Create a column to get Course Published final date

CoursePublishedDate =
CALCULATE(
    MAX('Milestones'[FinalDueDate]),
    FILTER(
        'Milestones',
        'Milestones'[Course ID] = EARLIER('Milestones'[Course ID]) &&
        'Milestones'[Milestone] = "Course Published"
    )
)

1. Now adjust all related milestones based on this:

CascadeAdjustedDueDate =
VAR ThisMilestone = Milestones[Milestone]
VAR FinalPubDate = Milestones[CoursePublishedDate]
VAR NewDate =
    SWITCH(
        TRUE(),
        ThisMilestone = "Assessment Checks", FinalPubDate - 7,
        ThisMilestone = "Programme Checks", FinalPubDate - 7,
        ThisMilestone = "Library Checks", FinalPubDate - 7,
        ThisMilestone = "EdTech Checks", FinalPubDate - 7,
        ThisMilestone = "Course Built", FinalPubDate - 14,
        ThisMilestone = "Syllabus Received", FinalPubDate - 28,
        Milestones[FinalDueDate]
    )
RETURN
    IF(
        Milestones[IsOffPeriod] &&
        Milestones[Milestone] <> "First Lecture",
        NewDate,
        Milestones[FinalDueDate]
    )

1. If you want to separately adjust Syllabus Received based on Course Built only (if Course Published isn’t adjusted):

Create a similar logic using Course Built as reference.

🧪 Step 7: Testing the Output

Now that you’ve done all of the above:

• Add a matrix or table in Power BI.

• Display columns:

  • Course ID

  • Milestone

  • Original Due Date

  • AdjustedDueDate

  • FinalDueDate

  • CascadeAdjustedDueDate

  • IsOffPeriod

Compare with your Excel sample. ✅ You should see green rows matching!

📘 Summary of What We Built

Logic Tool Notes

⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
🚀Let’s keep building smarter, data-driven solutions together! 🚀 [Explore More]

Hi v-sdhruv,

Thanks for providing this solution.

When I implemented it, it worked to a point, however, there were some dates that remained unchanged. Usnig the advice you gave and the original suggestion from GrowthNatives, I further customised your inputs and mine using Deepseek AI and I was eventually successfully in making the final adjustments I needed.

For the benefit of others, the latest code that is updated in the "CascadeAdjustedDueDate" calculated column is below. This is what incorporates all the suggestions and inputs to finalise the logic I needed.

Correct Due Date = 
VAR ThisMilestone = Milestones[Milestone]
VAR CurrentCourse = Milestones[CourseKey]
VAR FinalPubDate = RELATED(CoursePublishedDates[CoursePublishedDate])
VAR FinalCourseBuiltDate = RELATED(CourseBuiltDates[CourseBuiltAdjusted])
VAR IsCurrentMilestoneOffPeriod = Milestones[IsOffPeriod]
VAR OriginalDueDate = Milestones[FinalDueDate]
VAR OriginalDueYear = YEAR(OriginalDueDate)
VAR OriginalDueMonth = MONTH(OriginalDueDate)

// Determine if date is in December or January off-period
VAR IsDecOffPeriod = OriginalDueMonth = 12
VAR IsJanOffPeriod = OriginalDueMonth = 1

// Calculate correct adjustment date
VAR AdjustedDecDate = 
    IF(
        IsJanOffPeriod,
        DATE(OriginalDueYear - 1, 12, 5), // Jan 2026 → Dec 2025
        DATE(OriginalDueYear, 12, 5)      // Dec 2025 stays in 2025
    )

VAR SecondWeekDecMonday = DATE(YEAR(AdjustedDecDate), 12, 8)
VAR OffPeriodEnd = DATE(YEAR(AdjustedDecDate) + IF(IsJanOffPeriod, 0, 1), 1, 1)
VAR IsPubDateOffPeriod = LOOKUPVALUE(Milestones[IsOffPeriod], Milestones[Milestone], "Course Published", Milestones[CourseKey], CurrentCourse)
VAR SyllabusLogicStartDate = DATE(2025, 8, 5)

// Adjusted publication date
VAR AdjustedPubDate = 
    IF(
        IsPubDateOffPeriod,
        AdjustedDecDate,
        FinalPubDate
    )

// Check if any check milestones are in off period
VAR HasCheckMilestonesInOffPeriod =
    COUNTROWS(
        FILTER(
            FILTER(
                Milestones,
                Milestones[CourseKey] = CurrentCourse &&
                (Milestones[Milestone] = "Assessment Checks" ||
                 Milestones[Milestone] = "EdTech Checks" ||
                 Milestones[Milestone] = "Library Checks" ||
                 Milestones[Milestone] = "Programme Checks") &&
                Milestones[IsOffPeriod] = TRUE
            ),
            TRUE()
        )
    ) > 0

// Calculate adjusted dates
VAR AdjustedCourseBuiltDate = 
    IF(
        ThisMilestone = "Course Built",
        IF(
            IsPubDateOffPeriod,
            AdjustedPubDate - 14,
            IF(
                IsCurrentMilestoneOffPeriod,
                IF(
                    HasCheckMilestonesInOffPeriod,
                    AdjustedDecDate - 7,
                    AdjustedDecDate
                ),
                FinalCourseBuiltDate
            )
        ),
        FinalCourseBuiltDate
    )

VAR AdjustedSyllabusReceivedDate = 
    IF(
        ThisMilestone = "Syllabus Received",
        IF(
            FinalPubDate >= SyllabusLogicStartDate,
            IF(
                IsPubDateOffPeriod,
                AdjustedPubDate - 28,
                AdjustedCourseBuiltDate - 14
            ),
            OriginalDueDate
        ),
        BLANK()
    )

// Base calculation
VAR BaseDate =
    SWITCH(
        TRUE(),
        ThisMilestone = "Assessment Checks", AdjustedPubDate - 7,
        ThisMilestone = "Programme Checks", AdjustedPubDate - 7,
        ThisMilestone = "Library Checks", AdjustedPubDate - 7,
        ThisMilestone = "EdTech Checks", AdjustedPubDate - 7,
        ThisMilestone = "Course Built", AdjustedCourseBuiltDate,
        ThisMilestone = "Syllabus Received", AdjustedSyllabusReceivedDate,
        OriginalDueDate
    )

// Final adjustment
VAR AdjustedDate =
    IF(
        IsCurrentMilestoneOffPeriod,
        SWITCH(
            TRUE(),
            NOT(IsPubDateOffPeriod) && HasCheckMilestonesInOffPeriod && 
            (ThisMilestone = "Assessment Checks" || 
             ThisMilestone = "EdTech Checks" || 
             ThisMilestone = "Library Checks" || 
             ThisMilestone = "Programme Checks"),
            AdjustedDecDate,
            BaseDate
        ),
        BaseDate
    )

// Final validation
VAR ProposedDate = 
    IF(
        ThisMilestone = "First Lecture",
        OriginalDueDate,
        AdjustedDate
    )

VAR IsProposedDateInOffPeriod = 
    ProposedDate >= SecondWeekDecMonday && 
    ProposedDate <= OffPeriodEnd

RETURN
    IF(
        IsProposedDateInOffPeriod,
        OriginalDueDate,
        ProposedDate
    )