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

     

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
7 years ago

Hi,

This calculated column formula works

=if(ISBLANK(CALCULATE(MAX(Data[Time]),FILTER(Data,Data[Name]=EARLIER(Data[Name])&&Data[Service]=EARLIER(Data[Service])&&Data[ServiceIndex]<EARLIER(Data[ServiceIndex])))),BLANK(),Data[Time]-CALCULATE(MAX(Data[Time]),FILTER(Data,Data[Name]=EARLIER(Data[Name])&&Data[Service]=EARLIER(Data[Service])&&Data[ServiceIndex]<EARLIER(Data[ServiceIndex]))))

Hope this helps.