how to get date value from another row in the same table?

[maxnuij]

Hi,
I have a challenge to get a date value from another row in the same table.

I have a table with timestamps and status changes (old status and new status). What I try to achieve is getting the start date and the end date of a specific status (in progress). Since I only have the timestamp of the "change date", I'll have to find timestamp of the row where the NewValue is "in progress", and add that to the row where OldValue is "in progress". That way I have the lead time of that status. sounds simple but a few extra challenges:

- I use a DirectQuery (and want to continue doing so) so can not edit the PowerQuery and e.g. creating an index is not allowed;

- Table of the timestamps contain more than 1mio results what causes error's in visuals;

- An Item can have the in progress state multiple times, what should result in multiple leadtimes which I want to summarize in another formula once this one is fixed.

Formula that (I think) is very close (but doesn't work in the visuals since query is exceeding 1mio results):

PreviousDate = 
VAR PreviousRow =
            FILTER(
            all(TIMEDURATION_REQUEST_TASK),
            COUNTROWS(
                TOPN(
                    1,
                    FILTER(
                        TIMEDURATION_REQUEST_TASK,
                        TIMEDURATION_REQUEST_TASK[ChangedOnDateTime] < EARLIER(TIMEDURATION_REQUEST_TASK[ChangedOnDateTime])
                        &&TIMEDURATION_REQUEST_TASK[NewValue] = "In Progress"
                    ),
                TIMEDURATION_REQUEST_TASK[ChangedOnDateTime],ASC
    )))
VAR PreviousValue =
    MINX(PreviousRow,[ChangedOnDateTime])
RETURN PreviousValue

  here a prt screen of an example of the data I have to work with:

Number is the unique ticket the specific timestamps are referring to.

Hope someone has an answer 🙂 

[v-jiewu-msft]

Hi @maxnuij ,

If I understand correctly, the issue is that you want to get value from another row. Please try the following methods and check if they can solve your problem:

1.You can try to limit the amount of data being processed through aggregation or filter.

2.You need to rely on DAX calculations. Enter the following DAX formula.

PreviousDate =
VAR CurrentRow = SELECTEDVALUE(TIMEDURATION_REQUEST_TASK[ChangedOnDateTime])
VAR PreviousRow =
    FILTER(
        ALL(TIMEDURATION_REQUEST_TASK),
        TIMEDURATION_REQUEST_TASK[ChangedOnDateTime] < CurrentRow
            && TIMEDURATION_REQUEST_TASK[NewValue] = "In Progress"
    )
VAR PreviousValue = MAXX(PreviousRow, TIMEDURATION_REQUEST_TASK[ChangedOnDateTime])
RETURN PreviousValue

Best Regards,

Wisdom Wu