Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
4 years ago
Solved

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 ...
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
4 years ago

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's avatar
Ashish_Mathur
Super User
4 years ago

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.