Adding criteria to a filter pane

[SeanB]

I have an issue and have been really struggling to find a solution.

I have a data table with advisors as my dimension and then just basic KPI's for the advisors.

I also have a master table for all the advisors with their names, the month and their teams which they were in that month.

My issue is that an advisor can change teams from month to month so an advisor may have 4 different teams in a 12 month period so in April advisor 1 will be on team 1 and then by August they may have changed to team 2 however on the master sheet we keep the advisor in team 1 For April and then mark them as being in team 2 as of August.

When I put  a filter pane to filter my data table by team, if I run the report as of August so my date picker is set for August, I would want for advisor 1 to show as being in team 2 however they show as in team 1 because this is the first team instance they have on the advisor master so this is what the filter pane picks up.

Is there a way so that the filter pane will look for the advisors team based on the date selected for the report.

I do already have a variable set up which gets the date from the date picker. 

[stevedark]

Hi @SeanB 

What does your data model look like?

If your advisors table is separate to the main fact table you will need to ensure you have the date in the key between the two tables, creating a composite key between, for instance:

LOAD
   AdvisorID & '-' & date(Month, 'YYMM') as AdvisorKey,

It all depends on your data as to the best way of dealing with things.

Steve

https://www.quickintelligence.co.uk/blog/

[Chanty4u]

Try this 

=Lookup('Team', 'Advisor Name', [Advisor Name], 'Start Date', '<=' & Num(Max(Date)), 'End Date', '>=' & Num(Max(Date)))

[SeanB]

My advisor master is linked into my 2 data sources so that when a team is selected in the filter pane it effects the data in the table below.

A key would not work in my data model as I have a master calendar dictating the date of the report.

Is there no way to do an expression in the filter pane so that the month on the advisor master matches the month selected in my date range?