Search for the smallest closest date to the filter

[Dommnn]

Hi all!

There are two tables:
1. A loaded table with the dates of changes in the prices of the product:
01.03.2021 100
02.05.2021 120
04/04/2022 130
06.06.2022 160

2. A standard table with a calendar that is used for the filter.

Necessary:
In the interface when setting the filter by date, display the price closest to the filter date (the price that acted on the date of the filter)

Example 1: The value of the filter "05.05.2022" should be displayed the next price to this date: "130" (was set 05.05.2022)
Example 2: The value of the filter "07.03.2021" should be displayed the next price to this date: "100" (was set 03.03.2021)

The loading script is undesirable, better through the interface.
I will be very glad to help

[vinieme12]

as below

temp:
load Date(date#(date,'MM.DD.YYYY')) as DATE,value inline [
date,value
01.03.2021,100
02.05.2021,120
04.04.2022,130
06.06.2022,160
];


Calendar:
  LOAD Date(_DateStart+(Iterno()-1),'$(DateFormat)' ) as [DATE]
               While (_DateStart+(Iterno()-1)<=_DateStop);
        LOAD
               Floor(Min(Fieldvalue('DATE',RecNo()))) as _DateStart,
               Floor(Max(Fieldvalue('DATE',RecNo()))) as _DateStop
        AUTOGENERATE FieldValueCount('DATE');

exit Script;

In chart:

Dimensions:

recordid

DATE

Measure:

sum({1}value)  // for checking

the below measure will show results for next possible date

sum({<DATE={"$(=Min({< recordid=p({<DATE={">$(=Max(DATE))"}>} recordid) , DATE= >}total DATE))"} >} value)

you can add a condition as below

=if(sum(value),sum(value), sum({<DATE={"$(=Min({< recordid=p({<DATE={">$(=Max(DATE))"}>} recordid) , DATE= >}total DATE))"} >} value) )

[Dommnn_2022]

Thanks a lot! it works!

I have one additional  task , i have similar table with the name of the goods.
The name changes in time, below is an example.
Accordingly, i need to find out what name the product had at a certain period of time. 

i need to know  results for previous possible date  

For example if  value of the filter  is "19.07.2021" should be displayed  "Name2" (was set  at 15.05.2021 and changed in 21.04.2022 )

Table: 

17.03.2021 Name1
15.05.2021 Name2
21.04.2022 Name3
18.06.2022 Name4

[vinieme12]

just the below changes 

sum({<DATE={"$(=Max({< recordid=p({<DATE={"<$(=Max(DATE))"}>} recordid) , DATE= >}total DATE))"} >} value)