Variable lookup

[#1]

I want to find the value of a field in a table based on the value of today also in the same table.

Table (PeriodCalendar) has these fields:

          Date          FinPer

          15/04/15     01

          16/01/15     01

          04/02/15     02

          etc....

So I need a variable that defines the FinPer for today

e.g. if today is 04/02/15 I want my variable to be 02

Thanks for your help

[gautik92 · Accepted solution]

Have a look at this

[MK_QSL]

Define your variable as below

=FirstSortedValue(FinPer,-Date(Date#(Date,'DD/MM/YY')))

or

=FirstSortedValue(FinPer, -IF(Date(Date#(Date,'DD/MM/YY'))=Date(Today()),Date(Date#(Date,'DD/MM/YY'))))