Forum Discussion

ThomasDay's avatar
ThomasDay
Impactful Individual
10 years ago
Solved

Tagging most recently reported data

Yes, this seems like it should be simple but it's elusive to me.   I have a table of healthcare provider data--thousands of them.  There is one "report demographics record" per report for each data...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    10 years ago

    OK, first in "HOSP10_2014_RPT" table, create a custom column with the formula:

    Year = RIGHT([Report Dt],4)*1

    Then, what I did was create another table "Providers" that just lists the providers, Provider 1, Provider 2, Provider 3. You could use a query that just grabs distinct values for "Who" against the same data source you are pulling from right now. Relate the two tables on the "Who" columns.

     

    Create a custom column in this new "Providers" table with the formula:

     

    Max = MAXX(RELATEDTABLE(HOSP10_2014_RPT),HOSP10_2014_RPT[Year])

    Finally, back in "HOSP10_2014_RPT" table, create the following custom column:

    CurrentRept = IF([Year] = RELATED(ProviderTable[Max]),True,False)

     

ThomasDay's avatar
ThomasDay
Impactful Individual
10 years ago

Actually, even if this worked, it wouldn't trap for the situation:

 

Provider 1 2014

Provider 1 2013

Provider 1 2012

 

So I'll need to presort the table I guess--and if there's more than one row--put the True in the first one only.  Anyway..if you have a tip on tagging the latest date for any provider that would be great.

 

Thanks,

Tom