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

[mouzzampk]

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

[#2]

@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.

[#3]

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

[#4]

@SergeiBaklan 

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

[#5]

@mouzzampk 

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

[#6 · Accepted 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

[#7]

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

[#8]

@mouzzampk , you are welcome