Next Occurrence of Item

[pgd]

I have a Power BI 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? Also, does anyone know which graphic would best represent this cycle time metric? I'm new to Power BI. 

[v-yadongf-msft · Accepted solution]

Hi @pgd ,

This is my test table:

Create a measure:

Datediff_check_out = 
VAR current_date =
    MAX ( 'Table'[Check out date] )
VAR previous_date =
    CALCULATE (
        MAX ( 'Table'[Check out date] ),
        FILTER (
            ALL ( 'Table'[Check out date] ),
            'Table'[Check out date] < current_date
        )
    )
RETURN
    IF (
        ISINSCOPE ( 'Table'[Check out date] ),
        DATEDIFF ( previous_date, current_date, DAY ),
        BLANK ()
    )

 You can get the difference days between the check-out and  next time check-in dates for every Barcode of book:

Best regards,

Yadong Fang

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