Forum Discussion
Get records between two dates
I have a table that has as a key the name of the person and has a date data this table is called patients.
| Number | Date | Stature | Weight |
| Santiago | 12/05/2022 | ||
| Laura | 1/03/2021 | ||
| Marcela | 6/07/2021 |
In another table you have a historical record of the height and weight of patients, this table is called historicalPatients.
| Number | Date | Stature | Weight |
| Santiago | 1/01/2022 | 140 | 55 |
| Santiago | 1/03/2022 | 142 | 56 |
| Santiago | 1/05/2022 | 145 | 57 |
| Laura | 1/01/2021 | 150 | 52 |
| Laura | 1/04/2021 | 153 | 53 |
| Laura | 1/06/2021 | 156 | 54 |
| Marcela | 1/04/2021 | 135 | 56 |
| Marcela | 1/06/2021 | 137 | 58 |
| Marcela | 7/08/2021 | 139 | 60 |
It is required to feed the data of the patient table comparing the record where the date of the patient table is greater than the historical one but not greater than the following record, that is, for the case of marcela, the date in the patient table is 6/07/2021 and in the historical table it is fulfilled that the date is greater than the record 1/04/2021 and the record 1/06/2021 should be taken the second since it is the closest and once this record is identified, bring the height and weight data in the patient table. The result is expected to be as follows:
| Number | Date | Stature | Weight |
| Santiago | 12/05/2022 | 145 | 57 |
| Laura | 1/03/2021 | 150 | 52 |
| Marcela | 6/07/2021 | 137 | 58 |
Hi Syndicate_Admin ,
Please check these formulas.
latest date = CALCULATE(MAX(HistoricalPatients[Date]),FILTER(HistoricalPatients,HistoricalPatients[Date]<=Patients[Date]&&HistoricalPatients[Number]=Patients[Number])) Stature = CALCULATE(MAX(HistoricalPatients[Stature]),FILTER(HistoricalPatients,HistoricalPatients[Number]=Patients[Number]&&HistoricalPatients[Date]=Patients[latest date])) Weight = CALCULATE(MAX(HistoricalPatients[Weight]),FILTER(HistoricalPatients,HistoricalPatients[Number]=Patients[Number]&&HistoricalPatients[Date]=Patients[latest date]))Best Regards,
Jay
5 Replies
Hi,
Write these calculated column formulas in the Patient table
=LOOKUPVALUE(Historicalpatients[Stature],Historicalpatients[Date],CALCULATE(max(Historicalpatients[Date]),FILTER(Historicalpatients,Historicalpatients[Number]=EARLIER(Patients[Number])&&Historicalpatients[Date]<=EARLIER(Patients[Date]))),Historicalpatients[Number],Patients[Number])=LOOKUPVALUE(Historicalpatients[Weight],Historicalpatients[Date],CALCULATE(max(Historicalpatients[Date]),FILTER(Historicalpatients,Historicalpatients[Number]=EARLIER(Patients[Number])&&Historicalpatients[Date]<=EARLIER(Patients[Date]))),Historicalpatients[Number],Patients[Number])Hope this helps.
I tried but I get the error I provided a table of several values where a single value was expected, it is important to comment that in the historical table there is a row where the name is found but we do not have associated dates yet then empty values can be found, is it possible that this is not serving the lookupvalue formula?
You can now fix the error by adding an IF clause for the when the date value in historical is empty leave the cell blank but if you find a date data bring the height and weight, thank you very much.
Hi Syndicate_Admin ,
Please check these formulas.
latest date = CALCULATE(MAX(HistoricalPatients[Date]),FILTER(HistoricalPatients,HistoricalPatients[Date]<=Patients[Date]&&HistoricalPatients[Number]=Patients[Number])) Stature = CALCULATE(MAX(HistoricalPatients[Stature]),FILTER(HistoricalPatients,HistoricalPatients[Number]=Patients[Number]&&HistoricalPatients[Date]=Patients[latest date])) Weight = CALCULATE(MAX(HistoricalPatients[Weight]),FILTER(HistoricalPatients,HistoricalPatients[Number]=Patients[Number]&&HistoricalPatients[Date]=Patients[latest date]))Best Regards,
Jay
