How to get a use the maximum date of each given year in a set expression

[HarshChachara]

Hi I am looking for the following output

Year  | MaxDate        |  Sales on MaxDate

2020 | 31/03/2020   |   10

2019 | 31/12/2019   |    20

2018 | 31/12/2018   |   30

I can't get to a formulation/set analysis that allows me to use the maxdate in the set analysis.

Please advice.

[MayilVahanan · Accepted solution]

Hi Harsha,

Try like below

Sum({<Date={$(=Chr(39)&Concat(Aggr(Date(Max(Date)),Year),chr(39)&','&chr(39))&chr(39))}>}Sales)