i have a star schema with a multikpi visualisation and i want to get the max position (rank_invest) of the max date of the previous Year (rank_invest) of the category 1 (investor) for each invested (dimension) , here is an example of the model:
the output desired: if the user selects any date of 2021 then:
- invested_10 : 1,5 (previous year 2020, max position 1,5 , cat_1)
- invested_20 : 1,8 (previous year 2020, max position 1,8 , cat_1)
i have avoided the set analysis because each invested could have a specific max date of the last year as @rubenmarin has mentioned in a previous answer, but the problem with if() is that i can't choose a non selected date, when the user selects N, i can't use (non selected ) values of N-1.
the difference now is that i want the max value of the last Year, if the user chooses N, ithe result should be max Date of N-1, and as i said above, i couldn't use if here because the values aren't in the selection (N-1).