Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Difference between two rows with with multiple filters

I have the following table which has an Index, and a ServiceIndex (indexed against Name & Service):   Index ServiceIndex Name Service EnterOrExit Time 0 1 John Wash Exit 10:00:00 ...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Anonymous ,

     

    Try this DAX calculated Column:

    Column 4 = VAR Index = 'Table'[Index]
VAR Reference = 'Table'[Service]
VAR Prevtime =
    CALCULATE (
        FIRSTNONBLANK ( 'Table'[Time], TRUE () ),
        FILTER ( 'Table', 'Table'[Index] = Index - 1 && 'Table'[Service] = Reference )
    )
RETURN
    IF (
        ISBLANK ( Prevtime),
        blank(),
        'Table'[Time] - Prevtime
    )

    Here is my output based on your screenshot:

     

    Let me know if this works.

    Thanks,

    Tejaswi

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Anonymous ,

 

Try this DAX calculated Column:

Column 4 = VAR Index = 'Table'[Index]
VAR Reference = 'Table'[Service]
VAR Prevtime =
    CALCULATE (
        FIRSTNONBLANK ( 'Table'[Time], TRUE () ),
        FILTER ( 'Table', 'Table'[Index] = Index - 1 && 'Table'[Service] = Reference )
    )
RETURN
    IF (
        ISBLANK ( Prevtime),
        blank(),
        'Table'[Time] - Prevtime
    )

Here is my output based on your screenshot:

 

Let me know if this works.

Thanks,

Tejaswi