cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
MyNameHere Frequent Visitor
Frequent Visitor

How to mark Active record in fact table?

I have time series data for Sources A and B.

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.

 

Any help appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
MyNameHere Frequent Visitor
Frequent Visitor

Re: How to mark Active record in fact table?

Got it.
For people playing along, 

Last Date for Source = CALCULATE(
   LASTDATE( 'TimeSeries'[Date] ),
   ALL( 'TimeSeries'[Date])
)

 

Status = IF(
   CALCULATE(
      [Date Of Last Visit],
      FILTER(
         'TimeSeries',
         'TimeSeries'[SourceKey]=EARLIER('TimeSeries'[SourceKey])
      )
   )
   = 'TimeSeries'[Date],
   IF (
      DATEDIFF('TimeSeries'[Date], TODAY(), day) < 28,
      "Current",
      "Expired"
   ),
   "History"
)

View solution in original post

4 REPLIES 4
Super User
Super User

Re: How to mark Active record in fact table?

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.
MyNameHere Frequent Visitor
Frequent Visitor

Re: How to mark Active record in fact table?

Thanks Matt.

 

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. 

 

Edit:

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.

 

 

MyNameHere Frequent Visitor
Frequent Visitor

Re: How to mark Active record in fact table?

Maybe there is another simplier method but currently it means I can simplify this:

 

SumPlacePropertyX = SUM('TimeSeries'[PropertyX] )

 

PlacePropertyX Status =
   SUMX (
      VALUES ( 'TimeSeries'[PlaceKey] ),
      CALCULATE (
      [SumPlacePropertyX],
      LASTNONBLANK (
         'Calendar'[DateKey],
         [SumPlacePropertyX]
      )
   )
)

 

to:

 

PropertyXStatus = CALCULATE(
   SUM('TimeSeries'[PropertyX] ),
   'TimeSeries'[Status] = "Current"
)

MyNameHere Frequent Visitor
Frequent Visitor

Re: How to mark Active record in fact table?

Got it.
For people playing along, 

Last Date for Source = CALCULATE(
   LASTDATE( 'TimeSeries'[Date] ),
   ALL( 'TimeSeries'[Date])
)

 

Status = IF(
   CALCULATE(
      [Date Of Last Visit],
      FILTER(
         'TimeSeries',
         'TimeSeries'[SourceKey]=EARLIER('TimeSeries'[SourceKey])
      )
   )
   = 'TimeSeries'[Date],
   IF (
      DATEDIFF('TimeSeries'[Date], TODAY(), day) < 28,
      "Current",
      "Expired"
   ),
   "History"
)

View solution in original post

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)