Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Count Serial if Outgoing Reps Time is between Sign In Start and Finish Time

Copper Contributor

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

7 Replies

@mouzzampk 

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=10459...

Thank you

@Sergei Baklan 

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. 

 

mouzzampk_0-1700325821848.png

 

I hop the attached spreadsheet will give more information.

 

Thank you

 

@mouzzampk 

I added two calculated columns

image.png

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] <= finishTime

With that first measure

Done In Time:=CALCULATE( [Total Letters], 'Outgoing Reps'[IsInTime] )

For another time is similar.

Not sure if performance will be critical.

best response confirmed by mouzzampk (Copper Contributor)
Solution

@mouzzampk 

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

image.png

Thank you so much and much appreciated for giving your time on this. This is extremely helpful

Both solutions working great.

Thanks

Best regards,
mouzzampk
1 best response

Accepted Solutions
best response confirmed by mouzzampk (Copper Contributor)
Solution

@mouzzampk 

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

image.png

View solution in original post