Forum Discussion

pgd's avatar
pgd
Frequent Visitor
3 years ago
Solved

Return difference between two dates if criteria matches

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). 

 

Need Help
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    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.

2 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    3 years ago

    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)

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    3 years ago

    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.