Find a value in a table based on a date

Based on a date, I must search a table for a value from another column, but the date is not necessarily exact, it can be within a range or be greater than the last one in the table.

For example

VUT - Table

|Since |Value|
| 19/02/2014 | 127 |
| 25/02/2015 | 150 |
| 11/02/2016 | 177 |
| 25/02/2017 | 300 |

Depending on the date of a transaction, I must take the value of the โ€œValueโ€ field and perform a calculation, but the date of the transaction can be anyone and not necessarily the one specified in the table, each record establishes a range and must take the "Value "depending on where you are

I would appreciate your comments.

Solved Solved
0 6 529
1 ACCEPTED SOLUTION

Have to change MIX to MAX on the SELECT โ€ฆ the list for this SELECT takes the rows where Dates ar greater and equal to Since โ€ฆ but the closest Since is the MAX

View solution in original post

6 REPLIES 6

Steve
Participant V

What is that calculation?

Anyone?

Then how do you know what date to use?

How?

This makes no sense at all.

The problem youโ€™re trying to solve is entirely unclear.

Thanks.

Iโ€™ve to search in the table with a date variable.

the date must be greater than any โ€ฆ but it must not be greater than the next โ€ฆ if it exists

If I look for the date 30/06/2016 the result should be 177

If I search 30/3/2017 the result should be 300

if i search a date before the first Since Date Value โ€ฆ it have to give an error

The dates establishes the limit of a range of dates โ€ฆ and Value is the result expected

Thanks for your time

So, given a date, find the table row with the closest lower date and return the value for that row?

ANY(
  SELECT(
    VUT[Value],
    (
      [Since]
      = MIN(
        SELECT(
          VUT[Since],
          ([Since] <= [_THISROW].[Date])
        )
      )
    )
  )
)

Something likes โ€ฆ i will try with your code โ€ฆ thanks a lot

Have to change MIX to MAX on the SELECT โ€ฆ the list for this SELECT takes the rows where Dates ar greater and equal to Since โ€ฆ but the closest Since is the MAX

Hello Steve,
This is my first post in this community.
I have a similar problem.
Here is my table:
Table Name: MilkPrice

|Price Date|Price per litre|

|10/10/2021|โ‚น43.00|
|10/15/2021|โ‚น45.00|

Based on your recommendation, i tried the following formula for fetching the Milk price as on date entered in another table.
ANY(
SELECT(
MilkPrice[Price per litre],
(
[Price Date]
= MIN(
SELECT(
MilkPrice[Price Date],
([Price Date] <= [_THISROW].[Date])
)
)
)
)
)

However, it gives me the following error message: โ€œArithmetic expression โ€˜([Price Date] <= [_THISROW].[Date])โ€™ does not have valid input typesโ€ (Please see the link for screenshot: appsheet error.png - Google Drive)

Could you please help me to correct it?
Thanks a lot in advance.

Top Labels in this Space