Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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).
Solved! Go to 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.
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.
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)
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |