need help creating a measure that finds the previous event date for a given event

[ukeasyproj]

Hi I am currently working in Direct Query mode (unfortunately cannot use import mode for our use case).

The goal is to create a column, that finds the last date an event occurred for a given task. (see Previous Date column).

In import mode, I was able to get a solution by creating a calculated column that does:

Previous date = CALCULATE(MAX(Sheet1[date of event]),FILTER(Sheet1,Sheet1[date of event]<EARLIER(Sheet1[date of event]) && Sheet1[task id] = EARLIER(Sheet1[task id])))

Unfortunately this does not work in Direct Query mode since you cannot use the Calculate function in a calculated column.  So I am wondering if there is a workaround for anyone using Direct Query?

[Anonymous]

Does not work, return last event date not the previous!!!

[ukeasyproj · Accepted solution]

I have managed to create a measure:

test = CALCULATE(
MAX( Sheet1[date of event] ),
FILTER( ALL(Sheet1) ,
MAXX( FILTER( Sheet1, EARLIER( Sheet1[date of event] ) < Sheet1[date of event] && EARLIER( Sheet1[task id]) = Sheet1[task id]), Sheet1[date of event] )
)
)