Earlier() function

[paivers]

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())

 

[Jacob Barnett]

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>

 

[Jacob Barnett]

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)

 

[Jacob Barnett]

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)

 

[paivers]

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!

=if([Readmit]="Y",MAXX(
FILTER(Charges,(Charges[Readmit Index Visit]="Y" &&
'Charges'[YH_MRN] = EARLIER('Charges'[YH_MRN]) &&
'Charges'[AdmitDate] < (EARLIER('Charges'[Discharge Date])) &&
'Charges'[Discharge Date] < (EARLIER('Charges'[Discharge Date])) &&
'Charges'[Discharge Date] >= (EARLIER('Charges'[AdmitDate])-30) ))
,'Charges'[Discharge Date]
),BLANK())