Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.