Expression filtering based on user input

Question

I wish to aggregate Score * Revenue for the most recent entry per organization, considering only those entries where the date is less or equal to a user-defined variable vSelectedDate that is from the DatePicker user input in the Sheets editor, after load script has completed.

Organisation Name	Date	Revenue	Score
Apple	06/02/2024	300	5
Apple	06/02/2023	200	4
Apple	06/02/2022	100	3
Banana	01/01/2024	50	10
Banana	01/01/2023	70	10
Banana	01/01/2022	100	11

E.g.vSelectedDate= 30/12/2023. Desired selection (for visualisation):

Organisation Name	Date	Revenue	Score
Apple	06/02/2023	200	4
Banana	06/02/2023	70	10

Desired expression result: 200 * 4 + 70 * 10 = 1500

Is there a possible solution or workaround for what I need?

I have tried:

Sum(
  Aggr(
    If(
      Rank(Max(Date)) = 1 and Date <= $(vSelectedDate),
      Score * Revenue
    ),
    [Organisation Name]
  )
)

However I understand that it doesn't work because Max(Date) needs to be calculated within an aggregated context specific to each Organisation Name, but without an explicit aggregation phase separating the determination of Max(Date) from its ranking, the expression cannot correctly isolate and rank the latest date per organization.

Thanks in advance!

Answer 1

You can use this expression:

Sum(
    Aggr(
         FirstSortedValue({<Date={"<=$(vSelectedDate)"}>} Score * Revenue, -Date) 
    ,[Organisation Name]
   )
 )

It works in various kinds of objects like table or KPI:

Data used:

Data:
Load *
Inline [
Organisation Name,  Date,   Revenue,    Score
Apple,  '06.02.2024',   300,    5
Apple,  '06.02.2023',   200,    4
Apple,  '06.02.2022',   100,    3
Banana, '01.01.2024',   50,     10
Banana, '01.01.2023',   70,     10
Banana, '01.01.2022',   100,    11
];