Forum Discussion

Yamabushi's avatar
Yamabushi
Icon for Helper I rankHelper I
7 years ago
Solved

Calculating difference in minutes based on variables

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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