Using Max (User Selected Date) to display Pivot table dimension

[#1]

Hello,

I'm running into an issue where I need to take the value from one table " UltimateBroker" that coincides with whatever the max date the user selects from the calendar.

So if a user picks a date range from 1/1/2015 to 3/31/2015, I need to take the value in UltimateBrokerName that matches the max date which in this case would be 3/31/2015. The UltimateBrokerName is being used in a pivot table as a dimension. Another criteria is if the user picks a date range where the specific broker has 2 ultimate brokers. From 1/1/2015 to 2/15/2015 it shows a specific broker to have Ultimate Broker A, from 2/16/2015 - 12/31/3999 that same broker now rolls up to Ultimate Broker B. In this situation ALL data from 1/1/2015 - 3/31/2015 will roll up to Ultimate Broker B since the max date = Ultimate Broker B row in the table.

How/Where do I put logic that can change what UltimateBrokerName(dimension) each broker (another dimension) rolls up to and then show MoneyAmount(expression) based on what date range the user picks.

Pivot Table currently looks like this, we are open to change design if the UltimateBrokerName needs to become an expressions.

UltimateBrokerName

               Broker Name1          $$$$

               Broker Name2          $$$$

UltimateBrokerName 2

               Broker Name 3          $$$$

               Broker Name 4          $$$$

[jolivares]

Asuming you need to get the name of the broker for max date, try with this expression:

FirstSortedValue(Broker,-Aggr(Max(Date),Date))