Forum Discussion

ThomasDay's avatar
ThomasDay
Icon for Impactful Individual rankImpactful 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
Icon for Impactful Individual rankImpactful 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