Override filters

[cheryl0316]

These are the columns in the Date Info table.

First Day of Week: Sunday is the first day of week

First Day of Week_RankDESC: Rank based on First Day of Week in descending order (i.e., the latest week has the lowest rank = 1).

CurrentYear_WeekMinDate: Earliest date of the current week.

PriorYear_WeekRank: Rank of the same week prior year

PriorYear_WeekMinDate: Earliest date for the same week prior year

PriorYear_WeekRankAdd10 = PriorYear_WeekRank + 10

PriorYear_WeekMinDateAdd10Weeks = Earliest date for the week corresponding to PriorYear_WeekRankAdd10.

https://drive.google.com/file/d/1N-bLYLpyjs3K7uQHWm9QDC4DPpydXRS-/view?usp=sharing

1. I can understand why the current year information is returned, but I don't understand why PriorYear_WeekRank is returned while PriorYear_WeekMinDate and PriorYear_WeekMinDateAdd10Weeks are not.

e.g. End Date = 19 Jul 2025 (Sat)

For the row where the First Day of Week is 13 Jul 2025, it includes dates from 13 to 19 Jul 2025. Why PriorYear_WeekRank can be returned when the date is outside of the above range?

PriorYear_WeekRank =

VAR CurrentYearWeekMinDate = [CurrentYear_WeekMinDate]

VAR step1 = CurrentYearWeekMinDate - WEEKDAY(EDATE(CurrentYearWeekMinDate, -12), 2) +7

VAR step2 = EDATE(step1, -12)

RETURN

CALCULATE(MIN('Date'[First Day of Week_RankDESC]), 'Date'[Date] = step2)

2. How to return PriorYear_WeekMinDate and PriorYear_WeekMinDateAdd10Weeks

Thanks in advance:)

[sanalytics]

@cheryl0316 
This is simple filter context logic.. You have missed something in Date Table. Below is the code can be helpful

Correct_PYWMindate = 
VAR PriorYearWeekRank = [PriorYear_WeekRank]
VAR _Result = 
CALCULATE(
    MIN('Date'[Date]), 
FILTER( ALL( 'Date' ),
    'Date'[First Day of Week_RankDESC] = PriorYearWeekRank)
)
VAR _Result2 = 
CALCULATE(MIN('Date'[Date]), 
'Date'[First Day of Week_RankDESC] = PriorYearWeekRank,
REMOVEFILTERS( 'Date')
)
RETURN
_Result

both _Result and _Result2 should work. Attaching the files for your reference.

Hope it helps.

Regards,

sanalytics