# Earlier() function

#### paivers

##### Board Regular
Hi,
I am trying to use the Earlier function to do some complex date compares. Basically I have a series of events with Begin and End dates. They go in sequences, so the events are marked as either an EarlierEvent or LaterEvent (I had to reverse the EarlierEvent operators to meet the syntax). What I want to do is for each LaterEvent, find the most recent earlierEvent, but only if it ended with 30 days of the later event.
Here’s my formula. It always returns blanks. Any ideas?
- Pete

=if([LaterEvent]="Y",MAXX(
FILTER('FactTable',("Y"=Earlier(FactTable[EarlierEvent]) && 'FactTable'[FactKey] = EARLIER('FactTable'[FactKey]) && 'FactTable'[BeginDate] >= (EARLIER('FactTable'[EndDate])) && 'FactTable'[BeginDate] <= (EARLIER('FactTable'[EndDate])+30) ))
,'FactTable'[EndDate]
),BLANK())

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Pete,

99 times out of 100 EARLIER() is something you would use in a calculated column as it requires a row context. That said it can be used in a measure but it's pretty complex, Javier's post shows how: Can EARLIER be used in DAX measures? | Javier Guillén

I devised a quick example by guessing what your dataset looked like from your formula which can be found here: https://docs.google.com/file/d/0Bz5yMU2oooW2bW1QSDZoM3lkRVE/edit?usp=sharing

I created a measure which effectively filters the EndDate column on just the previous 30 days from the BeginDate in the current context and returns the date from the EndDate column if there is one.

<max(facttable[begin date])&&
=CALCULATE(
MAX(FactTable[End Date]),
FILTER(
ALL('facttable'),
FactTable[End Date]<max(facttable[begin date])&&
FactTable[End Date]>=max(FactTable[Begin Date])-30)
)

I'll concede that given the likely complexities of your data and the way you want to present the answer this may not be exactly what you are looking for but it works on my dataset and should give you the pattern you need!

Jacob</max(facttable[begin></max(facttable[begin>

Not sure what happened in my original post but it should look like:

End Date Prior Event:=CALCULATE(
MAX(FactTable[End Date]),
FILTER(
ALL('facttable'),
FactTable[End Date]~max(FactTable[Begin Date])&&
FactTable[End Date]>=max(FactTable[Begin Date])-30)
)

There is something in the forum that is cutting stuff out but the ~ in the example above should be < (less than)

Not sure what happened in my original post but it should look like:

End Date Prior Event:=CALCULATE(
MAX(FactTable[End Date]),
FILTER(
ALL('facttable'),
FactTable[End Date]~max(FactTable[Begin Date])&&
FactTable[End Date]>=max(FactTable[Begin Date])-30)
)

There is something in the forum that is cutting stuff out but the ~ in the example above should be < (less than)

Thanks Jacob, that was great that you figured that out. I did implement it in my model, it was on its way but not quite there, and I realized that I needed it as a caculated field as I link it to a date dimension for reporting. After doing a lot of messing with it, I finally found that I had the wrong concept of Earlier(), I thought it was the "other" rows, but no that's the row you are on - strange name for it. Anyway, with a little changing I came up with the following working expression. The names are different as I simplified the expression, but this is a hospital application where admit and discharge dates are the begin and end dates.

Anyway, thanks for your help, and although this is a Pennsylvania hospital, I was raised in Manchester, so nice to hear from a fellow mancunian!

'Charges'[YH_MRN] = EARLIER('Charges'[YH_MRN]) &&
'Charges'[Discharge Date] < (EARLIER('Charges'[Discharge Date])) &&
,'Charges'[Discharge Date]
),BLANK())

Replies
3
Views
372
Replies
3
Views
1K
Replies
1
Views
365
Replies
7
Views
2K
Replies
3
Views
1K

1,203,059
Messages
6,053,294
Members
444,650
Latest member
bookendinSA

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back