With or Without FILTER in measure

Hello cheryl0316 ,

Q1:

CALCULATE(SUM(Revenue[Revenue]), 'Date'[First Day of Week_RankDESC] = 1)

This only applies a value-level filter on the column, if the column is in the current filter context.

BUT: If the column 'Date'[First Day of Week_RankDESC] is not directly part of the active filter context (for example affected by a slicer or visual), this may fail silently or return unexpected results. This version assumes there's already a context over the 'Date' table that it can intersect with.

CALCULATE(
SUM(Revenue[Revenue]),
FILTER('Date', 'Date'[First Day of Week_RankDESC] = 1)
)

This forces DAX to:

Iterate the entire Date table -> Keep only the rows where RankDESC = 1 -> Use that set of dates to filter the revenue.

This works regardless of prior filters, and is considered the safer, more consistent approach.

When using calculated columns or virtual filters, prefer FILTER() inside CALCULATE() unless you're sure the simple 'Table'[Column] = value filter will behave correctly.

If this solved your issue, please mark it as the accepted solution. ✅

Hi, 
The first part is explained here. 
The big difference is that CALCULATE uses syntax sugar. The measure should look like this.
CALCULATE(
SUM(Revenue[Revenue]),
FILTER(
ALL('Date'),
'Date'[First Day of Week_RankDESC] = CurrentYearWeekRank
)
)
The arguments of CALCULATE looks like conditions but are table. Clear explained by the SQLBI
https://www.youtube.com/watch?v=Tk-7gBt9CDE 
Here more info about the syntax suger
https://exceleratorbi.com.au/simple-filters-and-syntax-sugar-in-dax/ 
The second part, maybe later. 

Hi cheryl0316 ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you JJ_3 FBergamaschi anilelmastasi for the prompt response.

I have tried replicating the scenario using sample data.Please go through the attached PBIX file for your reference.

Thank you.

These two expressions seem similar, but they behave differently because of how CALCULATE handles filters:

• The first version uses a boolean filter expression directly:
  'Date'[First Day of Week_RankDESC] = CurrentYearWeekRank
  This is interpreted as a table filter on the column and interacts more directly with existing filters from slicers or visuals. It's more efficient and often preferred for performance.

• The second version wraps the condition inside a FILTER function:
  FILTER('Date', 'Date'[First Day of Week_RankDESC] = CurrentYearWeekRank)
  This constructs a row context inside FILTER and returns a new filtered table. This filter doesn’t always override existing filters applied by visuals or slicers as you might expect. If the slicer already restricts the date range, the FILTER function only works within that pre-filtered context.

Slicers and visual-level filters supersede the filters inside CALCULATE unless you explicitly remove them using functions like ALL, REMOVEFILTERS, or ALLSELECTED.

Even though you've selected an end date of 2 Jul 2025, the data still shows Thu–Sat because of how slicers and the DAX engine work:

• If your measure or table uses a field that ignores the slicer's filter, such as by using ALL('Date') or ALL('Date'[Date]), then it's no longer limited to the slicer's date range—even if the slicer UI looks like it is.

• Alternatively, if you're using a calculated column like 'Date'[First Day of Week_RankDESC] to determine week groupings, that grouping may include dates outside your slicer selection because the measure isn't strictly bound to the slicer unless you explicitly tell it to be.

If you want the visual to respect the end date from the slicer, then don’t remove or override the slicer filter unless absolutely necessary.
If you want to ignore the slicer and always compute the full current week and prior year week, then use CALCULATE with ALL('Date') to clear slicer filters and define the logic yourself.

Try these measures :

Current Week Revenue :=
VAR CurrentWeekRank =
MINX(ALL('Date'), 'Date'[First Day of Week_RankDESC])

RETURN
CALCULATE(
SUM(Revenue[Revenue]),
FILTER(
ALL('Date'),
'Date'[First Day of Week_RankDESC] = CurrentWeekRank
)
)

Same Week Last Year Revenue :=
VAR CurrentWeekRank =
MINX(ALL('Date'), 'Date'[First Day of Week_RankDESC])

VAR SameWeekLastYearRank =
CurrentWeekRank + 52 -- Adjust based on how your ranking is structured

RETURN
CALCULATE(
SUM(Revenue[Revenue]),
FILTER(
ALL('Date'),
'Date'[First Day of Week_RankDESC] = SameWeekLastYearRank
)
)