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:)

[v-pnaroju-msft]

Hi cheryl0316,

We would like to follow up and see whether the details we shared have resolved your problem.
If you need any more assistance, please feel free to connect with the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft · Accepted solution]

Thankyou, @sanalytics, for your response.

Hi cheryl0316,

Thank you for your follow-up.

Based on my understanding, the reason is that the PriorYear_WeekRank is calculated using scalar logic based on a single date and is not restricted by visual or slicer filters at the time of evaluation.

In the DAX code for PriorYear_WeekRank, the CALCULATE expression references a specific prior date from step 2, so it evaluates directly based on that date match, without depending on the surrounding rows or visual filters such as slicers. If step 2 exists in your Date table, then its rank, i.e., PriorYear_WeekRank, will be returned regardless of whether that date is visible in the visual or falls within the selected range.

The key difference is that scalar value calculations, like PriorYear_WeekRank, are evaluated independently, whereas lookup-based logic, such as PriorYear_WeekMinDate, requires finding matching values, which may be affected by filters.

We hope that the information provided will help resolve the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

[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

[cheryl0316]

Thank you for your reply. Do you know why PriorYear_WeekRank can be returned when the date is outside of the above range?