Forum Discussion
Count Serial if Outgoing Reps Time is between Sign In Start and Finish Time
Hi,
I have four tables
Outgoing Reps
Sign In
Calendar
Officer
Calendar and Officer are dimension tables and Outgoing Reps and Officer are linked to Calendar and Officer table.
I am looking for a DAX solution to "Count PCN if Outgoing Reps Time is between Sign In Start and Finish Time"
Sign In
Date Officer Start Finish O/T Start O/T Finish
| 23/10/2023 | User 1 | 08:50:00 | 15:00:00 | 04:30:00 | 06:30:00 |
| 26/10/2023 | User 2 | 08:00:00 | 16:00:00 | 06:45:00 | 07:45:00 |
| 26/10/2023 | User 3 | 16:00:00 | 18:00:00 | ||
| 27/10/2023 | User 4 | 08:00:00 | 16:05:00 | 06:45:00 | 07:45:00 |
| 27/10/2023 | User 1 | 08:00:00 | 16:05:00 | 06:45:00 | 07:45:00 |
Outgoing Reps
Serial Date Time Officer
| EA13243243 | 02/03/2023 | 07:08:00 | User 1 |
| EA13243244 | 05/03/2023 | 21:40:00 | User 1 |
| EA13243245 | 05/03/2023 | 07:12:00 | User 2 |
| EA13243246 | 05/03/2023 | 15:26:00 | User 3 |
| EA13243247 | 09/03/2023 | 13:05:00 | User 3 |
| EA13243248 | 09/03/2023 | 13:50:00 | User 4 |
I am looking for DAX functon to Count number of Serial recorded by Officers during Normal Hours and O/T Hours from Sign In Sheet.
Expected Outcome
Normal Hours O/T Hours
20 15
I hope this gives more information.
Thank you
Depends on your entire model and how you use it, perhaps its better to make preparations with Power Query. In attached file that's Outgoing Reps time slots.
With it we have quite simple measures, PivotTable looks like
It's better if you share small sample file and clarify what the PCN is, as well as Outgoing Reps Time, O/T Start, etc.
Date ranges in both tables are different, there is no link between them.
- Hi Sergei Baklan, thank you for looking into this.
Please download the attachment. I hope this will help.
https://docs.google.com/spreadsheets/d/1OiJakJOhKzTCGo26CeP3u-l7zpSZvWx0/edit?usp=sharing&ouid=104599929744989992299&rtpof=true&sd=true
Thank you I have Outgoing Reps table linked to Officer and Calendar Table and Sign In linked to Officer and Calendar but I am getting this message.
I hop the attached spreadsheet will give more information.
Thank you
I added two calculated columns
DateTime is obvious, just o simplify a bit calculations. Another one
IsInTime= VAR startTime = CALCULATE ( VALUES ( 'Sign In'[StartDateTime] ), TREATAS ( VALUES ( 'Sign In'[Officer] ), 'Outgoing Reps'[Officer] ) ) VAR finishTime = CALCULATE ( VALUES ( 'Sign In'[FinishDateTime] ), TREATAS ( VALUES ( 'Sign In'[Officer] ), 'Outgoing Reps'[Officer] ) ) RETURN 'Outgoing Reps'[DateTime] >= startTime && 'Outgoing Reps'[DateTime] <= finishTimeWith that first measure
Done In Time:=CALCULATE( [Total Letters], 'Outgoing Reps'[IsInTime] )For another time is similar.
Not sure if performance will be critical.
