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.
Hi,
Trying to find the "Break Time" between the shift finish time & the next shift start time per employee.
Attached is an excample of the shifts worked by one employee in a typical day.
Solved! Go to Solution.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@CNENFRNL I would like the results from your calculated column 'shifted' up a row.
Instead of the first row blank, the last should be blank.
I've been tinkering with your formula but havebeen unable to achieve this.
Time Between Calls =
VAR __prev =
MAXX (
TOPN (
1,
FILTER (
'powerbi114 view_finance',
'powerbi114 view_finance'[carer_id]
= EARLIER ( 'powerbi114 view_finance'[carer_id] )
&& 'powerbi114 view_finance'[Date] = EARLIER ( 'powerbi114 view_finance'[Date] )
&& 'powerbi114 view_finance'[Finish Time]
<= EARLIER ( 'powerbi114 view_finance'[Start Time] )
),
'powerbi114 view_finance'[Finish Time]
),
'powerbi114 view_finance'[Finish Time]
)
RETURN
IF ( NOT ISBLANK ( __prev ), 'powerbi114 view_finance'[Start Time] - __prev )
@CNENFRNL Thks for your help. I just needed to add an earlier date filter to your code to get it to do what I needed.
Thks again. Much appreciated.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@CNENFRNL That works well in sample file but i think having other days and employees throws it off.
For every day I need to show it for each employee. For their first & last shift that day it should return blank.
This will give you a column with the previous end time so you can get the difference with a subtraction:
Add a custom column (sub in previous step for #"Changed Type")
let a = [Sched_Start] in List.Max(List.Select(#"Changed Type"[Sched_Finish], each _ <= a))
it will be a little more complex with different employee ids
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 |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |