Different behaviour of date slicer in combination of REMOVEFILTERS

[vinhnguyen_phan]

I observe a difference in the way the slicer work with date and time data in combination with using of REMOVEFILTERS in a measure. 

Below is an demonstration with financials sample data

I create a measure as below 

sum_sale_all_products = CALCULATE(SUM(financials[ Sales]), REMOVEFILTERS(financials[Product]))

The report is as below., The slicer on top set to Date field, use only Year and Month. The matrix below use Segment, Products and Date in rows

When the slicer is in vertical list or drop down mode, all the values of sum_sale_all_products are equal for each segment, which is as expected

 

When the slicer is in between/before/after mode, the values of _sum_sale_all_products are different, in the same segment, which is not expected

It seem that in between mode, for each product, only the time range in which the sale data of that product exist is selectec. The measure is then calculated with that time range for all products. Because some products does not have sale data in some particular month, the sale of other products in the other month is not counted into the measure.

Is this a bug or I mis-understood something? Thank you

[Samarth_18 · Accepted solution]

Hi @vinhnguyen_phan 

What I feel the difference in results between the "Vertical List/Drop-down" and "Between/Before/After" modes arises from how Power BI interprets and applies the slicer selections: 

• Vertical List or Drop-down Mode: In this mode, the slicer generally applies a single selection for each filter context (e.g., a specific year and month). This means the filter context for the measure sum_sale_all_products remains consistent across all segments and products.
• Between/Before/After Mode: In these modes, Power BI attempts to dynamically filter data based on the time range selected. This can lead to different filter contexts being applied to different segments and products based on the availability of data within the selected time range.

[Samarth_18 · Accepted solution]

Hi @vinhnguyen_phan 

What I feel the difference in results between the "Vertical List/Drop-down" and "Between/Before/After" modes arises from how Power BI interprets and applies the slicer selections: 

• Vertical List or Drop-down Mode: In this mode, the slicer generally applies a single selection for each filter context (e.g., a specific year and month). This means the filter context for the measure sum_sale_all_products remains consistent across all segments and products.
• Between/Before/After Mode: In these modes, Power BI attempts to dynamically filter data based on the time range selected. This can lead to different filter contexts being applied to different segments and products based on the availability of data within the selected time range.

[vinhnguyen_phan · Accepted solution]

@Samarth_18 most probably your are right. By changing my model to like this and set the Slicer to Date[Date], the report work as expected.

[vinhnguyen_phan]

My model only has one table. Here I modified your pbix file a bit to reproduce the phenomenon

- Remove the dropdown slicer

- Change the timeline slicer to the Date field of the Financials table

- Add Category column

- Change Table to Matrix.

- Add Category into Matrix row on top of Product

I cannot upload the pbix file to this forum

[lbendlin]

Cannot reproduce.  How does your data model look like?