Access control reports

[jacosmit]

Would dearly appreciate a set of fresh eyes.

I've been tasked with building a report that provides some insights as based on data from an access control db (sample data attached) The idea is to provide monthly reports that would be provided to the divisions that would highlights key stats.

These include:

1- List of staff per division that spent most time on site during the month (top 10)

2- List of staff per division that spent the least amount of time on site during the month (bottom 10)

My thought process was to create measures that would

1) Write earliest time per day per employee into new column

2) Write latest time per day per employee into another new column

3) Minus latest time with earliest time in a third new column and rank to get top and bottom lists.

Problem is, when I work with "CALCULATE" I am getting unexpected results, especially when trying to apply multiple filters to the measure for example:

Earliest Time = CALCULATE(MINX(Query1,Query1[TIME]),ALLEXCEPT(Query1,Query1[USER_ID],Query1[DATE].[Day]))

There has to be a better way to go about this!?

DATE	TIME	TERMINAL	DIVISION	USER_ID
Friday, 02 November 2018	06:32:34	Staff Boom IN	Division 01	Joe Soap
Friday, 02 November 2018	06:33:07	Boom Basement Ent	Division 01	Joe Soap
Friday, 02 November 2018	06:34:20	Basement Lobby	Division 01	Joe Soap
Friday, 02 November 2018	06:35:33	3rd Floor North	Division 01	Joe Soap
Friday, 02 November 2018	07:03:45	3rd Floor North	Division 01	Joe Soap
Friday, 02 November 2018	08:18:20	3rd Floor North	Division 01	Joe Soap
Friday, 02 November 2018	08:43:59	Visitor Boom IN	Division 02	Jan Niemand
Friday, 02 November 2018	08:44:30	Staff Parking IN	Division 02	Jan Niemand
Friday, 02 November 2018	08:49:11	Turnstile North 1 IN	Division 02	Jan Niemand
Friday, 02 November 2018	09:49:48	3rd Floor North	Division 01	Joe Soap
Friday, 02 November 2018	09:52:08	3rd Floor North	Division 01	Joe Soap
Friday, 02 November 2018	10:58:37	3rd Floor North	Division 01	Joe Soap
Friday, 02 November 2018	12:32:18	3rd Floor North	Division 01	Joe Soap
Friday, 02 November 2018	13:07:08	3rd Floor North	Division 01	Joe Soap
Friday, 02 November 2018	13:34:59	Turnstile North 1 OUT	Division 02	Jan Niemand
Friday, 02 November 2018	13:38:49	Staff Parking OUT	Division 02	Jan Niemand
Friday, 02 November 2018	13:39:09	Staff Boom OUT	Division 02	Jan Niemand

[v-danhe-msft]

Hi @jacosmit ,

What is your desired reuslt for your formula? Could you please post your desired result if possible?

Regards,
Daniel He

[jacosmit]

Hi Daniel

Something similar to the attached, per division.

MONTH	DIVISION	USER_ID	TIME ON SITE	RANKING
Apr-19	Division 01	Joe Soap	198:13:02	1
Apr-19	Division 01	Frank Noble	198:11:00	2
Apr-19	Division 01	Stella Lange	195:05:12	3
Apr-19	Division 01	Frank Niemand	185:00:23	4
Apr-19	Division 01	Terry Crews	156:00:15	5
Apr-19	Division 01	Tom Cruise	154:00:14	6
Apr-19	Division 01	Rob Nelson	150:10:15	7
Apr-19	Division 01	Ted Mosley	149:02:45	8
Apr-19	Division 01	Sean Els	148:00:15	9
Apr-19	Division 01	Tom Brady	147:15:32	10

[v-danhe-msft]

Hi @jacosmit ,

So could you mean the TIME ON SITE and RANKING column is your desired column? How the logic about the TIME ON SITE column?

Regards,

Daniel He

[jacosmit]

Hi Daniel

Yes, TIME ON SITE and RANKING would be what is required as output.

Logic with regards to the TIME ON SITE...... Conceptually I wanted to work out the earliest and latest time per person per day. Then subtract latest with earliest to get to the amount of time spent on site per day. Then add all the daily totals to get to the monthly total.

[jacosmit]

Any ideas?