Return difference between two dates if criteria matches

[pgd]

My dashboard linked to a SharePoint list. The sharepoint list has the following columns: the barcode value on the library book, who checked out the library book, the time they checked out the library book, the genre of library book, etc. 

I want to do a cycle time metric. I want to calcuate the time between when the library book was checked out (and scanned out of inventory) and when it was checked back in (the next time it appears scanned in the inventory). I need some type of unique identifier so that I can see when the book comes back to the library. I also need some type of formula that looks for the unique value and returns the difference between the check-out and check-in dates. Does anyone know the best way to approach this? 

Expected Results

The first time the book with barcode 565648076 appears is when it was checked out (on 11/21). The next time the barcode apperas is when it was checked back in (on 11/23). I want to return the difference between these two dates (only when the barcode matches). 

[v-zhangti · Accepted solution]

Hi, @pgd 

If you want to try to solve the problem with Measure, you can try the following methods.
Measure:

Difference = 
VAR _last =
    CALCULATE(MAX('Table'[Date]), 
        FILTER (ALL ( 'Table' ),
            [Book Barcode Value] = SELECTEDVALUE ( 'Table'[Book Barcode Value] )
                && [Date] < SELECTEDVALUE ( 'Table'[Date] ) ))
RETURN
    IF (
        _last <> BLANK (),
        DATEDIFF ( _last, SELECTEDVALUE ( 'Table'[Date] ), DAY ),
        0 )

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu]

@pgd 

you can create a column

diff = 
var _last= maxx(FILTER('Table','Table'[book barcode value]=EARLIER('Table'[book barcode value])&& 'Table'[date]<EARLIER('Table'[date])),'Table'[date])
return if (ISBLANK(_last),BLANK(),'Table'[date]-_last)