Calculating difference in minutes based on variables

[Yamabushi]

Hi,

I have an issue with finding the exact date difference between two transactions. But the difference must calculate the date difference between transactions of the same person AND put a 0 where there were 2 transactions at the exact same moment.

So far, I have 2 solutions, each solves one part of the problem.

Number 1  - put a 0 where there are 2 transactions at the same time:

Column 2 = 
VAR ind = 'Sample'[Index] - 1
VAR pre =
    CALCULATE (
        MAX ( 'Sample'[Date and time of visit] );
        FILTER ( 'Sample'; 'Sample'[Index] = ind )
    )
RETURN
    DATEDIFF ( pre; 'Sample'[Date and time of visit]; MINUTE )

Number 2 - calculate the time difference in transactions of the same person:

Column 3 = DATEDIFF(CALCULATE(MIN('Sample'[Date and time of visit]);FILTER('Sample';EARLIER('Sample'[Name])='Sample'[Name]));'Sample'[Date and time of visit];MINUTE)

So hopefully, a combination of these two is possible - calculate the difference in time for transactions of a particular person and put a zero where that person had 2 transactions at the same moment.

I hope my explanation is clear.

Regards

[v-shex-msft · Accepted solution]

HI @Yamabushi ,

You can use following calculate column formula to achieve your requirement:

Diff =
VAR _pIndex =
    CALCULATE (
        MAX ( Table[Index] ),
        FILTER (
            ALL ( Table ),
            [Index] < EARLIER ( Table[Index] )
                && [Name] = EARLIER ( Table[Name] )
        )
    )
VAR _pVisit =
    LOOKUPVALUE ( Table[Date and time of visit], Table[Index], _pIndex )
RETURN
    IF (
        _pVisit <> BLANK (),
        DATEDIFF ( _pVisit, Table[Date and time of visit], MINUTE ),
        0
    )

Regards,

Xiaoxin Sheng

[Yamabushi]

Hi,

This was exactly what I needed, thanks!