Changing dates in based on common values and requirements

[iammrishoabeid]

Hi Power BI Community,

I hope this finds you well.

I have a report that contains milestones or checks I need to complete per course. These checks are currently scheduled based on specific dates that are calculated using SQL based on a First Lecture check of that specific course. Each course has 7 checks in this order: Syllabus Received, Course Built, Assessment Checks, Programme Checks, EdTech Checks, Library Checks, Course Published, and First Lecture.

From 05 Aug 2025, the checks are changed to include 2 weeks between Syllabus Received and Course Built. This is up from 1 week before that date. All changes I need to make before are to be made for the milestones past 05 Aug 2025 ONLY. Previous milestones must remain exactly as they are.

I have searched online, perform some trials, and used AI to do the following:

1. create a dates table to be used for time intelligence. this table contains dates coming from UK Public Holidays API to incorporate bank holiday logic for all available dates in the table. This is based on 2 columns: IsHoliday - TRUE/FALSE and Holiday Name - Text.

2. merged the checks table - which includes all milestones for each course, with the dates table. I then expanded checks table to include IsHoliday, Holiday Name, and IsWeekend columns from the dates table.

3. using the extended columns in the checks table, I created a calculated column in Power Query called IsFutureDate - TRUE/FALSE. this columns marks all dates from 05 Aug 2025 onwards, as TRUE.

4. I also created a calculated column in DAX to adjust the due dates to new ones. this column is called AdjustedDueDate (see the code provided below for reference). However, this code is currently slightly flawed since after it makes an adjustment, it then subtracts an additional day, which is not the desired outcome. For instance, it changes Sat to Fri, then subtracts 1 additional day to Thu.

5. I also created another calculated column in DAX to mark all off days as TRUE and everything else as FALSE (see the code provided below for reference).

I need help implementing some date change logic in my dataset to change the AdjustedDueDate dates to new dates based on some constraints. The following is the desired outcome:

1. I would like to change all milestones that fall on weekends and public holidays (England and Wales) to the earliest working day. For instance, if a milestone falls on Sun, 14 Mar 2025, change it to Fri, 12 Mar 2025. And for the milestones that fall on days such as Easter Monday, move them to Thursday of the previous week, before Good Friday. this logic is kind of implemented using the AdjustedDueDate calculated column, but the code subtracts an additional day after it has made the appropriate changes. i want it to NOT subtract an additional day and ensure the change is adjusted only to the level of the earliest working day, and that is it.

2. There is a specific "off period" during which I would like milestones that fall during that time to be updated accordingly. This off period is from Mon of the second week of Dec to 01 Jan, every year. I have included this logic with the IsOffPeriod calculated column, which marks everything after the last working day of the first of week of Dec to 01 Jan (inclusive).

The changes I need to make during this time are:

i. move the milestone's due date to the last working day of the first week of Dec

ii. if the milestone is Course Published, update other milestones of the same course as follows

- move Assessment Checks, EdTech Checks, Programme Checks, and Library Checks to 7 days before the new Course Published milestone of the same course.

- move Course Built to 7 days before the new Assessment Checks milestone of the same course.

- move Syllabus Received to 14 days before the new Course Built milestone of the same course.

iii. if the milestone is Course Built, move the Syllabus Received milestone of the same course to 14 days before it.

I hope all that made sense. Additionally, I have provided the code snippets of the 2 calculated columns in the checks table, a .pbix file with a sample report to show how the data is linked, and an Excel spreadsheet that contains the desired outcome (SAMPLE DATA, DESIRED OUTCOME, AND PBIX FILE). I have highlighted in green all milestones that have changed, so you can see what has changed. you will notice that there are several courses with 7 milestones each, and only when a due date falls on a weekend, bank or public holiday, or an off period, are the changes applied. 

For reference:

i. each course has 7 milestones

ii. milestones are in a specific order from Syllabus Recevied, Course Built, Assessment Checks, Programme Checks, Library Checks, EdTech Checks, Course Published, and First Lecture.

iii. the number of days difference between milestones is Syllabus Received is 14 days before Course Built, which is 7 days before Assessment, EdTech, Library, and Programme Checks (all on the same day), which are 7 days before Course Published, and that is 21 days before First Lecture.

iv. you will notice in the desired outcome file that a course such as CD51 J SPR26 has a Syllabus Received date that does not fall on a weekend, public or bank holiday, or off period. however, it has been updated before previous milestone from Course Published were all updated.

CODE SNIPPETS:

The following code is for the calculated column in the checks table to adjust due dates from weekends and public holidays to the earliest working day.

AdjustedDueDate =

VAR OriginalDate = Milestones[Due Date]

VAR IsFuture = Milestones[IsFutureDate]

VAR IsWeekend =

    WEEKDAY(OriginalDate, 2) IN {6, 7}

VAR WeekendAdjustedDate =

    SWITCH(

        TRUE(),

        WEEKDAY(OriginalDate, 2) = 6, OriginalDate - 1,  -- Saturday → Friday

        WEEKDAY(OriginalDate, 2) = 7, OriginalDate - 2,  -- Sunday → Friday

        OriginalDate

    )

VAR IsHoliday = Milestones[IsHoliday]

VAR FinalAdjustedDate =

    IF(

        IsHoliday && WeekendAdjustedDate = OriginalDate,

        OriginalDate - 1,

        WeekendAdjustedDate

    )

RETURN

    IF(IsFuture, FinalAdjustedDate, OriginalDate)

########################

The following code is for a calculated column in the checks table to identify all days during the off period

IsOffPeriod =

VAR AdjDueDate = 'Milestones'[AdjustedDueDate]

VAR CutoffDate = DATE(2025, 8, 5)

-- If the date is before the cutoff, return FALSE (or keep existing logic if needed)

RETURN

    IF(

        AdjDueDate < CutoffDate,

        FALSE,

        VAR DueYear = YEAR(AdjDueDate)

        VAR DueMonth = MONTH(AdjDueDate)

        -- Determine the anchor year for the off period

        VAR AnchorYear = IF(DueMonth = 1, DueYear - 1, DueYear)

        -- Last working day of the first week of December (Dec 5)

        VAR Dec5 = DATE(AnchorYear, 12, 5)

        VAR StartOfOffPeriod =

            SWITCH(

                WEEKDAY(Dec5, 2),

                6, Dec5 - 1,  // Saturday → Friday

                7, Dec5 - 2,  // Sunday → Friday

                Dec5          // Weekday

            )

        -- End of off period: Jan 1 of the following year

        VAR EndOfOffPeriod = DATE(AnchorYear + 1, 1, 1)

        RETURN

            IF(

                AdjDueDate >= StartOfOffPeriod && AdjDueDate <= EndOfOffPeriod,

                TRUE,

                FALSE

            )

    )

########################

Please let me know in case there is any other information I can provide that could help with the resolution of this inquiry

Best regards,

Mrisho.

[GrowthNatives · Accepted solution]

Hi @iammrishoabeid ,
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

Adjust for weekends/holidays	DAX	Finds last working day
Mark milestones post-05 Aug	Power Query	Used to isolate adjustments
Off period detection	DAX	Handles 2nd week of Dec - Jan 1
Cascade adjustment	DAX	Uses SWITCH for logic by milestone type
Visual verification	Matrix visual	Compare expected vs actual

⭐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]

[v-sdhruv]

Hi @iammrishoabeid ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv]

Hi @iammrishoabeid ,

The issue you're facing is that the CourseBuilt and  Syllabus Received milestones are not cascading correctly because they depend on already-adjusted values within the same table, and DAX does not allow recursive dependencies in calculated columns.
1.Add CourseBuilt Dates table :
CourseBuiltDates =
ADDCOLUMNS(
SUMMARIZE(Milestones, Milestones[CourseKey]),
"CourseBuiltAdjusted",
CALCULATE(
MAX(Milestones[CascadeAdjustedDueDate]),
Milestones[Milestone] = "Course Built"
)
)

2.Create a relationship:

Milestones[CourseKey] --> CourseBuiltDates[CourseKey]

3.Then update  CascadeAdjustedDueDate logic-

CascadeAdjustedDueDate =
VAR ThisMilestone = Milestones[Milestone]
VAR FinalPubDate = RELATED(CoursePublishedDates[CoursePublishedDate])
VAR FinalCourseBuiltDate = RELATED(CourseBuiltDates[CourseBuiltAdjusted])

VAR NewDate =
SWITCH(
TRUE(),
ThisMilestone IN {"Assessment Checks", "Programme Checks", "Library Checks", "EdTech Checks"}, FinalPubDate - 7,
ThisMilestone = "Course Built", FinalPubDate - 14,
ThisMilestone = "Syllabus Received", FinalCourseBuiltDate - 14,
Milestones[FinalDueDate]
)
RETURN
IF(
Milestones[IsOffPeriod],
NewDate,
Milestones[FinalDueDate]
)

Hope this helps!
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You!

[iammrishoabeid · Accepted solution]

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
    )

[GrowthNatives · Accepted solution]

Hi @iammrishoabeid ,
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

Adjust for weekends/holidays	DAX	Finds last working day
Mark milestones post-05 Aug	Power Query	Used to isolate adjustments
Off period detection	DAX	Handles 2nd week of Dec - Jan 1
Cascade adjustment	DAX	Uses SWITCH for logic by milestone type
Visual verification	Matrix visual	Compare expected vs actual

⭐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]

[iammrishoabeid]

Hi @GrowthNatives,

Thanks for supporting me with this so quickly and for providing an excellent step-by-step guide with detailed instructions.

While making the changes you suggested, I successfully went as far as Step 6 point 1. At the point, I received a circular dependency error. I resolved this by creating the CoursePublishedDates as a calculated table using the code below:

CoursePublishedDates = 
ADDCOLUMNS(
    SUMMARIZE(Milestones, Milestones[CourseKey]),
    "CoursePublishedDate",
    CALCULATE(
        MAX(Milestones[FinalDueDate]),
        Milestones[Milestone] = "Course Published"
    )
)

and then I associated that table with the CascadeAdjustedDate column using RELATED(...) to link the column and the calculated table. That code is:

CascadeAdjustedDueDate = 
VAR ThisMilestone = Milestones[Milestone]
VAR FinalPubDate = RELATED(CoursePublishedDates[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],
        NewDate,
        Milestones[FinalDueDate]
    )

Upon making these changes, it successfully performed the date changes for all dates, except the Course Built and Syllabus Received milestones which are still referencing the original dates from the FinalDueDate column.

The table below those changes for a course whose milestones fall in the IsOffPeriod time. You will notice the last column, where I have indicated the corrected dates that should be updated for the Course Built and Syllabus Received milestones.

Course Code	Milestone	Due Date	CascadeAdjustedDate	Correct Dates
AM11   SPR26	Syllabus Received	18 Nov 2025	18 Nov 2025	07 Nov 2025
	Course Built	02 Dec 2025	02 Dec 2025	21 Nov 2025
	Assessment Checks	09 Dec 2025	28 Nov 2025	Correct
	EdTech Checks	09 Dec 2025	28 Nov 2025	Correct
	Library Checks	09 Dec 2025	28 Nov 2025	Correct
	Programme Checks	09 Dec 2025	28 Nov 2025	Correct
	Course Published	16 Dec 2025	05 Nov 2025	Correct
	First Lecture	06 Jan 2026	06 Jan 2026	Correct
CD52 S SPR26	Syllabus Received	18 Dec 2025	18 Dec 2025	21 Nov 2025
	Course Built	01 Jan 2026	01 Jan 2026	05 Dec 2025
	Assessment Checks	08 Jan 2026	08 Jan 2026	Correct
	EdTech Checks	08 Jan 2026	08 Jan 2026	Correct
	Library Checks	08 Jan 2026	08 Jan 2026	Correct
	Programme Checks	08 Jan 2026	08 Jan 2026	Correct
	Course Published	15 Jan 2026	15 Jan 2026	Correct
	First Lecture	02 Feb 2026	02 Feb 2026	Correct

Would you mind advising how I can ensure the Syllabus Received and Course Built milestones are updated accordingly?