I would like to use a calculated column to mark the active record (vs History) using a calculated column. This field will have 2 distinct values so cost will be minimal after initial calculation and some dependent measures will be significantly faster.
This seems like a pattern that once I understand it, ETL to Power BI will be simpler.
My first thought was:
Last Date for Source = CALCULATE( LASTDATE( 'TimeSeries'[Date]), ALL('TimeSeries'[Date]) )
and then compare that to the date of each row:
Status = IF( [Last Date for Source] = EARLIER('TimeSeries'[DateKey]), "Current", "History" )
But I get an error which I do not understand in a calculated column. "EARLIER/EARLIEST refers to an earlier row context which doesn't exist"
Removing the ALL and CALCULATE from the Last Date measure doesn't help.
The earlier/earliest functions can be confusing. They are used when you have 2 or more distinct row contexts that you are iterating over. A Calc Column has a row context, and so do other functions (like Filter). If you write a filter function in a Calc column, then the filter function has a row context and so does the calc column. The filter row context is nested inside the calc column row context. If you want to refer to the calc column row (as opposed to the filter row) then you need Earlier. But in your case you only have 1 row context (the calc column), so the earlier function is trying to refer to an earlier row context when there isn't one.
I don't think your Last Date for Source will work in a calc column. Try this instead
Last Date for Source = CALCULATE( LASTDATE( 'TimeSeries'[Date]), ALL('TimeSeries') )
Then try this for your other formula
Status = IF( [Last Date for Source] = 'TimeSeries'[Date], "Current", "History" )
There probably is a better way than stamping records this way, but this may help you with what you want to do
* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I'm not convinced it is the optimal solution either.
But now it works.
Need to reread about row context cos turns out I dont understand it yet.
I spoke too soon. Actually it's not quite right yet.
This method only makes the rows where date = maximum date in the table. I need maximum date in the table for each source > 'TimeSeries'[SourceKey]
eg. If the last date from Source A was '2016-02-19' and the last date for Source B was '2016-02-20'
then only records from '2016-02-20' would be marked as current.
What I need is Source A records to be marked current for '2016-02-19' also.
I would probably need an additional filter enforcing a maximum age (eg. 28 days) of data points also but maybe set the Status of those to "Expired". I consider that complication a seperate issue though.