Tagging most recently reported data

[ThomasDay]

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 set in a table of Reports.  It contains a FY for the report data submitted by the provider.  (2015 is the MOST current data set possible)

Who            Report Dt   Other stuff....

Provider 1     FY2013   

Provider 1     FY2014 

Provider 1     FY2015 *

Provider 2     FY2013 *

Provider 3     FY2015 *

Provider 3     FY2014

The * are the most current reported data sets.  Here's the code I was trying to use...on a calc column so the row context should iterate one row at a time I think.

CurrentRept = IF(
        CountRows (Values(HOSP10_2014_RPT[ProvdrNo]))=1,True,
IF(
        HOSP10_2014_RPT[FY(4)] >= 2015,True,
False))

Anyway, it never detects that some of the providers only have one data set and so they all get set to false if they haven't submitted FY2015

Obviously I'm not seeing what I should be seeing.  Any tips come to mind?

Thanks,

Tom

[Greg_Deckler · Accepted solution]

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]

Thank you @Greg_Deckler.  I like your answer very much--it's an important "pattern" of solution.  

I see this as using "scratch" tables for some of these sorts of calculations.  In this case a provider table of important attributes.  

With only 6K providers, does it matter if I end up making 100 of these indicators like last report date?  I can put all sorts of "cohort" types of indicators in it....bed size quintiles, financial performance quintiles, fips they serve, and so on.  It's surely more complex than that since quintiles will be sharded further with location/size/case complexity forming cohorts--and then quintiles within those.  I guess the learning curve will remain steep for awhile.

As an aside--entering DAX-land from more "traditional" programming...I was imagining I could step through each report row in the table sorted by provider and report date (DESC), and for each row see if there are other rows with the same provider number, then if so--tag the report row in some fashion.  No muss, no fuss.  

Perhaps there is a way to do that in DAX?  I was trying to figure how COUNTROWS and VALUES could give me an indication of the presence of other rows somehow, then maybe use EARLIER to get back to the report row to tag it.  If the sort were preserved--I'd be able to tag it properly as current, current -1, and so on.

If there are other approaches, I'd love to hear.  In the meantime, I'm going with this.

Thanks again smoupre.  This is a huge help.  Tom

[ThomasDay]

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