Get records between two dates

[Syndicate_Admin]

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

[v-jayw-msft · Accepted solution]

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

[Ashish_Mathur]

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.

[Syndicate_Admin]

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?

[Ashish_Mathur]

Based on the data that you shared, you can very clearly see that my formula is working.  If it is not working on your file, then there is someting in theree which you obviously have not shared in your original post.  Share a representative dataset and clearly show the problem. 

[Syndicate_Admin]

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.