The best brand in city and its value in the previous year

[piter89]

Hello,

I have a big problem in set analysis expression. I need the previous year's value for the best brand in town dimension in 2022.

My data:

LOAD * inline [
Year   |City  |Brand |Amount
2022   |London|Nike  |9
2022   |London|Adidas|12
2021   |London|Nike  |5
2021   |London|Adidas|1
2022   |Berlin|Nike  |10
2022   |Berlin|Adidas|6
2021   |Berlin|Nike  |46
2021   |Berlin|Adidas|1
] (delimiter is '|');

My table:

My expression for last column:

=Sum({<Year={'2021'},Brand={'$(=FirstSortedValue(Distinct Brand,-Aggr(Sum({< Year={'2022'} >}Amount),City,Brand)))'}>}Amount)

The expression returns the best brand for all cities, but I want the city dimension. The 'where' condition (Brand=) was computed before entering to the row dimension.

The expression for Amount column:

=Max(Aggr(Sum({< [Year]={'2022'}>}Amount), City, Brand))

The expression for the best brand column:

=FirstSortedValue(
      Distinct Brand,
      -Aggr(
        Sum({< Year={'2022'} >}Amount),
        City,
        Brand
      )
    )

Thanks in advance. 

[Taoufiq_Zarra]

@piter89  can you check :

in dimension : City

measures :

best brand ->  FirstSortedValue(Brand, -Aggr(Sum({<Year={"$(=Max(Year))"}>}Amount), Brand,City))

Amount ->  Max({<Year={"$(=Max(Year))"}>}Amount)

Amount 2021 -> Max({<Year={"$(=Max(Year)-1)"}>}Amount)

output:

[piter89]

I want to receive the amount in 2021 for the best brand in 2022 in the dimension of the city:
1. Berlin -> correct value is 46, because in 2022 the best brand of the city is Nike and Nike in 2021 had 46
2. London -> correct value is 1, because in 2022 the best brand of the city is Adidas and Adidas in 2021 had 1

Unfortunately, your expression skips the best brand for the city in 2022. The last column should show the score for the top brand from 2022 in 2021 in the city (single row).