calculate time elapsed between 2 dates from different rows with filtered conditions

[r_kender]

Hi, I'm A bit new in powerpivot and DAX and i've been trying to achieve the following without much success:
Say I have a powerpivot table wit the following columns and I want to ascertain the time elapsed from order to order (DateTime) for a specific item (Obj_ID)
For example substracting the DateTime of Item 1832 change #2 (order before last) from DateTime of the same item Change #5 (Last order), And so on and so on.
What would be the DAX syntax
[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]OBJ_ID[/TD]
[TD]DateTime[/TD]
[TD]Change #[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]1832[/TD]
[TD]

3/3/2015 9:22:30 AM

<tbody>
</tbody>

[/TD]
[TD]5[/TD]
[TD]Ordered[/TD]
[/TR]
[TR]
[TD]1832[/TD]
[TD]

3/2/2015 3:53:32 PM

<tbody>
</tbody>

[/TD]
[TD]4[/TD]
[TD]Recieved[/TD]
[/TR]
[TR]
[TD]1832[/TD]
[TD]

3/2/2015 3:45:23 PM

<tbody>
</tbody>

[/TD]
[TD]3[/TD]
[TD]Suggested[/TD]
[/TR]
[TR]
[TD]1832[/TD]
[TD]

2/22/2015 4:14:43 PM

<tbody>
</tbody>

[/TD]
[TD]2[/TD]
[TD]ORdered[/TD]
[/TR]
[TR]
[TD]1832[/TD]
[TD]

2/22/2015 4:14:05 PM

<tbody>
</tbody>

[/TD]
[TD]1[/TD]
[TD]Ordered[/TD]
[/TR]
[TR]
[TD]1899[/TD]
[TD]

4/16/2015 9:13:01 AM

<tbody>
</tbody>

[/TD]
[TD]5[/TD]
[TD]Ordered[/TD]
[/TR]
[TR]
[TD]1899[/TD]
[TD]

3/26/2015 10:25:06 AM

<tbody>
</tbody>

[/TD]
[TD]4[/TD]
[TD]Suggested[/TD]
[/TR]
[TR]
[TD]1899[/TD]
[TD]

3/26/2015 10:24:47 AM

<tbody>
</tbody>

[/TD]
[TD]3[/TD]
[TD]Suggested[/TD]
[/TR]
[TR]
[TD]1899[/TD]
[TD]

3/25/2015 1:11:20 PM

<tbody>
</tbody>

[/TD]
[TD]2[/TD]
[TD]Ordered[/TD]
[/TR]
[TR]
[TD]1899[/TD]
[TD]

3/23/2015 3:08:00 PM

<tbody>
</tbody>

[/TD]
[TD]1[/TD]
[TD]Ordered[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Ozeroth]

Hi,
I'm assuming you want to do this in a calculated column.

As a starting point, this code in a calculated column will give you the previous DateTime for the current OBJ_ID and Status. You can modify this to calculate the difference from current DateTime or only evaluate for Orders etc.
I'm assuming Change # and DateTime have corresponding orderings, so there are two possibilities:

Previous DateTime for Current OBJ_ID and Status =
CALCULATE (
    MAX ( Table2[DateTime] ),
    CALCULATETABLE (
        Table2,
        ALLEXCEPT ( Table2, Table2[Status], Table2[OBJ_ID] ),
        FILTER (
[COLOR=#ff0000][B]            VALUES ( Table2[Change #] ),[/B]
[B]            Table2[Change #] < EARLIER ( Table2[Change #] )[/B][/COLOR]
        )
    )
)

or

Previous DateTime for Current OBJ_ID and Status =
CALCULATE (
    MAX ( Table2[DateTime] ),
    CALCULATETABLE (
        Table2,
        ALLEXCEPT ( Table2, Table2[Status], Table2[OBJ_ID] ),
        FILTER (
[COLOR=#ff0000][B]            VALUES ( Table2[DateTime] ),[/B]
[B]            Table2[DateTime] < EARLIER ( Table2[DateTime] )[/B][/COLOR]
        )
    )
)

 

[r_kender]

Hi,
I'm assuming you want to do this in a calculated column.

As a starting point, this code in a calculated column will give you the previous DateTime for the current OBJ_ID and Status. You can modify this to calculate the difference from current DateTime or only evaluate for Orders etc.
I'm assuming Change # and DateTime have corresponding orderings, so there are two possibilities:

Previous DateTime for Current OBJ_ID and Status =
CALCULATE (
    MAX ( Table2[DateTime] ),
    CALCULATETABLE (
        Table2,
        ALLEXCEPT ( Table2, Table2[Status], Table2[OBJ_ID] ),
        FILTER (
[COLOR=#ff0000][B]            VALUES ( Table2[Change #] ),[/B]
[B]            Table2[Change #] < EARLIER ( Table2[Change #] )[/B][/COLOR]
        )
    )
)

or

Previous DateTime for Current OBJ_ID and Status =
CALCULATE (
    MAX ( Table2[DateTime] ),
    CALCULATETABLE (
        Table2,
        ALLEXCEPT ( Table2, Table2[Status], Table2[OBJ_ID] ),
        FILTER (
[COLOR=#ff0000][B]            VALUES ( Table2[DateTime] ),[/B]
[B]            Table2[DateTime] < EARLIER ( Table2[DateTime] )[/B][/COLOR]
        )
    )
)

Hi and thanks for your reply, I tried using your formula on a calculated column but unfortunately it seems that it is to heavy for my 32 bit Excel version.
The error I get is "Memory error: Allocation failure . If using a 32-bit version of the product, consider upgrading to the 64-bit version or increasing the amount of memory available on the machine."

Any ideas how to overcome?

 

[Ozeroth]

Hmm, try this as an alternative (still just computing the previous DateTime for current OBJ_ID & Status):

=
LASTNONBLANK (
    FILTER (
        CALCULATETABLE (
            SUMMARIZE ( Table2, Table2[DateTime] ),
            ALLEXCEPT ( Table2, Table2[OBJ_ID], Table2[Status] )
        ),
        Table2[DateTime] < EARLIER ( Table2[DateTime] )
    ),
    1
)

If it's still problematic, it may be better to calculate this column before loading to data model, e.g. using Power Query.