With or Without FILTER in measure

[cheryl0316]

I'm a bit confused about the FILTER function

There're two tables:

Date (with a one-to-many relationship) → Revenue

Sunday is the first day of week. Assume today is 5 Jul 2025 (Sat).

I need to create a table to compare revenue for current week (29 Jun - 5 Jul 2025) vs same week last year (30 Jun - 6 Jul 2025).

I’ve disabled the start date selection in the date slicer to prevent users from selecting an incorrect start date, which could disrupt the prior year calculation.

There is a column  - First Day of Week_RankDESC which ranks the first day of each week in descending order, so current week is 

MIN('Date'[First Day of Week_RankDESC]) 

I have two questions.

1. Why are the results different between the two measures below? I thought both were filtering the rank to the current week.

e.g.  CurrentYearWeekRank = MIN('Date'[First Day of Week_RankDESC])

Without FILTER

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

FILTER

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

2. If I select end date - 2 Jul 2025 (Wed), both tables still show revenue for Thur, Fri, Sat for current week.  Why?

Thanks in advance:)

https://drive.google.com/file/d/1n0UqUtWKyvxoL_0XuEqJY18ZzZzTzlwU/view?usp=sharing

[ashleyfiore]

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

[v-venuppu]

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.

[JJ_3]

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. 

[FBergamaschi · Accepted solution]

So you have two questions

1 With these two give different results?

Without FILTER

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

FILTER

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

The answer is that the first DAX expression is in reality

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

so the semantics are different due to the different FILTER statement, we might go more in details but this is a general answer.

2 why you get dates over your selection

you are seeing Thursdays etc from the subsequent week, that's what your DAX is saying, to take the minum  'Date'[First Day of Week_RankDESC] which is 2 on Thursday if you hide Thursdays etc from week 1

So you should reshape the DAX logic

Another notice: your Calendar table does not cover the entrie fact dates set, it ends on july 5 2025. Calendar should always be the minimum set of complete years to cover all your facts

We can go further in detail but this is a first feedback for you

Hope this helps starting figuring out

If this helped, please consider giving kudos and mark as a solution

@mein replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[cheryl0316]

For Q2 – I had been wondering where the revenue for Thur, Fri and Sat was coming from when I selected the end date as July 2, 2025 (Wed). I didn’t realize it was being pulled from the subsequent week (i.e., Week 2), so thank you for clarifying.

In that case, how can I keep MIN('Date'[First Day of Week_RankDESC]) based only on the date slicer selection, without it being affected by the Day of Week column in the table?

[FBergamaschi]

Happy to have been helpful

change the code in this way

CurrentYear_WeekRank =

VAR MaxDate = CALCULATE( MAX ( 'Date'[Date] ), REMOVEFILTERS( 'Date'[Day of Week], 'Date'[Day of Week No] ) )

VAR RankMaxDate = CALCULATE( MIN('Date'[First Day of Week_RankDESC]), 'Date'[Date] = MaxDate )

RETURN

RankMaxDate

And you get it working

Please,

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[cheryl0316]

Thank you:) Your formula works - Just curious about two things

1. Why does 'Date'[Day of Week No] need to be included in REMOVEFILTERS, even when the table doesn’t have the 'Date'[Day of Week No] column?

2. After updating the CurrentYear_WeekRank formula, it also affects PriorYear_WeekRank, and the table no longer shows a complete week of revenue for the prior year.
Is it possible to:

-Show Current Year Week Revenue only up to the selected end date (from the date slicer) -< which you did

-Still show the full week for Prior Year Week Revenue, even if the current end date is mid-week?

[FBergamaschi]

Hi agian

1 the reason is that your sorted the  'Date'[Day of Week] column by the 'Date'[Day of Week No] column, so you need to remove filtters from the couple of columns as anytime you filter  'Date'[Day of Week], you also filter 'Date'[Day of Week No]

2 of course it is possible :-), just change the below measure like the following code shows

PriorYear_WeekRank =

VAR CurrentYearWeekRank = [CurrentYear_WeekRank]

VAR WeekNo =

CALCULATE(

    MIN('Date'[Week of Year]),

    'Date'[First Day of Week_RankDESC] = CurrentYearWeekRank,

    REMOVEFILTERS( 'Date'[Day of Week], 'Date'[Day of Week No] )

)

RETURN

CALCULATE(

    MAX('Date'[First Day of Week_RankDESC]), ALL('Date'),

    'Date'[Year] = MAX('Date'[Year]) -1,

    'Date'[Week of Year] = WeekNo

)

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[FBergamaschi]

@cheryl0316 was my answer a solution?

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[cheryl0316]

Thank you - I just tried your formula and it worked.

[anilelmastasi]

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. ✅

[cheryl0316]

Thanks for answering.

For q2, the formula returns an error

I asked chatgpt to debug and the formula below works, but I want 'Date'[First Day of Week_RankDESC] to be dynamic and equal to

CurrentYear_WeekRank (i.e. MIN('Date'[First Day of Week_RankDESC]))

If user selects end date = 28 Jun 2025 (Sat) then CurrentYear_WeekRank = 2

If I change the filter part to 'Date'[First Day of Week_RankDESC] = CurrentYear_WeekRank, the formula doesn't work if I select end date = 25 Jun 2025 (Wed) and the table still shows revenue for Thur, Fri, Sat. Why?

Debug ver

CurrentYear_WeekRevenue_Filter =
VAR SelectedDates =
SELECTCOLUMNS(VALUES('Date'[Date]), "Date", 'Date'[Date])

VAR CurrentWeekDates =
SELECTCOLUMNS(
FILTER(
'Date',
'Date'[First Day of Week_RankDESC] = 1
),
"Date", 'Date'[Date]
)

VAR FinalDates =
INTERSECT(SelectedDates, CurrentWeekDates)

RETURN
CALCULATE(
SUM(Revenue[Revenue]),
KEEPFILTERS(FinalDates)
)