Forum Discussion
Get records between two dates
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
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.
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.