Trouble applying two date filters alongside multiple report-level filters

[pbiibp]

Hello, I’m having trouble applying two date filters alongside multiple report-level filters. I’ve tried multiple methods: DATESBETWEEN, DATESINPERIOD, FILTER, CALCULATE, ALL, ALLEXCEPT, IF, KEEPFILTERS, REMOVEFILTERS, etc. I can get individual components working properly, but not everything working correctly at the same time.

The pbix file can be downloaded from OneDrive at https://1drv.ms/u/s!AizFaawJP_Ads2L7sbT5o1qBm0Dv?e=PiL0Kn

Background & Requirements

1. There are seven report-level filters and five visuals on a page. Slicers are not used; all filters must be in the Filters pane or in Measures.

1. The seven report-level filters affect all five visuals equally, with one exception. The [Order Date] report-level filter only affects four of the visuals.

1. VisualX (shaded area in lower right) is not affected by the [Order Date] report-level filter. It always displays data from the previous 13 calendar months, regardless of the [Order Date] filter's settings. Put another way, VisualX is affected by all report-level filters except [Order Date].

Current Functionality

The required functionality is currently achieved using two iterations of the data table('OrdersTestData'), two iterations of the date table ('DateTable'), and six lookup tables that power six of the seven report-level filters. It is a cumbersome, inefficient solution.

Model view, Tables, and Relationships

Goal: Achieve required functionality using only tables 'OrdersTestData' and 'DateTable.'

1. Six report-level filters in Filters pane affect all visuals (Category, Customer, Region, Segment, Ship Mode, and State).
2. One report-level filter in Filters pane, [Order Date], affects all visuals except one (VisualX).
3. VisualX always displays previous 13 calendar months.
4. Slicers cannot be used. All filtering must be done via the Filters pane or in Measures.

The pbix file can be downloaded from OneDrive at https://1drv.ms/u/s!AizFaawJP_Ads2L7sbT5o1qBm0Dv?e=PiL0Kn

Thank you in advance for helping educate me! 🙂

[ghpbi]

Hello Kelly,

Thank you for taking the time to offer a solution. I downloaded your pbix; however, I have not been able to achieve the desired results yet. I need a little guidance from you or other forum viewers on these items, please.

• On Page 1, VisualX in the lower-right corner should always display data from the previous 13 calendar months. But in your pbix it is affected by changes to report-level filter [Order Date].
• Your ‘Date Table’ code references table ‘OrdersTestData13Months.’ That is one of the tables I want to remove from the model. I am confused; are there additional steps I’m missing?

To clarify, tables ‘OrdersTestData’ and ‘OrdersTestData13Month’ contain the exact same data. In Power Query, ‘OrdersTestData13Months’ is “Referenced” from ‘OrdersTestData.’ It is named ‘OrdersTestData13Months’ as a reminder that its data is always filtered to the previous 13 calendar months.

Using my original pbix, the examples below demonstrate the filtering at work. I am hoping to maintain that functionality without requiring “extra” table ‘‘OrdersTestData13Months.’

Hopefully the examples below help clarify the functionality and desired end result.

Example 1:

Example 1 : BEFORE

Example 1: AFTER

Example 2:

Example 2: BEFOREExample2: AFTER

Side note: The actual report I am trying to simplify contains many more data tables.

Forum viewers, I am open to alternate solutions you have to offer. Even responding, “This is not possible with the current version.” would be helpful to know.

Thank you! 🙂

[v-kelly-msft]

Hi  @pbiibp ,

Create a date table as below:

Date table = DISTINCT(UNION(DISTINCT('OrdersTestData'[Order Date]),DISTINCT('OrdersTestData13Months'[Order Date])))

Then create a relationship between table "date table" and "OrdersTestData","date table" and "OrdersTestData13 Month".

Finally you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!