Applying Historic Table to Monthly Report Data

[tschullo]

I have a difficult conundrum:

I have monthly report of account info with a Report Period  of Jan 2020, Feb 2020 etc.
(representing end of month snapshot)

I have another table that keeps a history of overrides to those accounts, including override type (A, B,C) and date of override.  these overrides can occur from zero to n number of times a month, so not necessarily every month.

I need to be able to show the current override type for a given report period selection.

If no selection is made, the latest override would be shown, if a selection is made, the max override date and value within that selection would be shown in my pivot table.

History table

Account	Override Date	Override type
101	2/2/2020	C
101	2/23/2020	C
102	1/1/2020	A
102	3/2/2020	C
103	12/12/2019	A

Account table (includes report date, but date is not in pivot)  Solution must respect Report Date selection

If no selection is made, max values are shown from history

Account	Current Override Code
101	C
102	A
103	A

If Feb 2020 is selected, values would be:

Account	Current Override Code
101	C
102	C
103	A

Any ideas?

[SerhanKaraer]

Hi Tschullo,

You can use FirstSortedValue function.

FirstSortedValue ([ distinct ] value, sort-weight [, rank ])

FirstSortedValue("Override type",-"Override Date")

sort-weight is ascending order by default, to turn it into descending just put minus sign before the field.

To show/hide "Override Date", you can write GetSelectedCount(YourReportPeriodField)=0 in "show column if " property in dimension/measure.