Forum Discussion
Count Serial if Outgoing Reps Time is between Sign In Start and Finish Time
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.
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
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
- 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
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.
- 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
