Calculate Dynamic average time

[Anonymous]

In my tabe i have each employees timgings for each day.

i would like to calculate the average timings for each employee as time range changes.

sample data:- 

Emp ID	Time
136225	9:07:31 AM
136225	9:36:45 AM
136225	9:42:04 AM
136225	9:42:11 AM
136225	9:42:49 AM
136225	9:44:49 AM
136225	9:46:44 AM
136225	9:48:23 AM
136225	9:50:23 AM
136225	9:51:27 AM
136225	9:53:49 AM
136225	9:54:15 AM
136225	9:56:39 AM
136225	10:05:49 AM
136225	10:18:34 AM
136225	10:43:22 AM
136225	12:35:58 PM

I have tried several formulas but i couldnt get what i am expecting.

1:-

AvgTime = CALCULATE(
		FORMAT(AVERAGE('Table'[Time]),"HH:MM:SS"),
		ALLEXCEPT('Table','Table'[Emp ID]))

But it is giving me the overall average for that perticular employee irrespective of the date range change from the slicer which is a column from the same table.

IF i change the range in begin_date slicer then the values wont change accordingly.

2:- 

AvgTime = CALCULATE(
            FORMAT(AVERAGE('Table'[Table]),"HH:MM:SS"),
            ALLSELECTED('Table'))

For above dax, it is giving me the average time if i select a perticular id .
but if i choose multiple ids from slicer then it is giving me the above values that is cumulative average for those selected ids.

How can i get the average for each indivudual employeees average time which gets changes as the date range values changes.

Please help.

Mohan V.

[Anonymous · Accepted solution]

Got by myself..

Below query worked.

AvgTime = CALCULATE(FORMAT(AVERAGE('Table'[Time]),"HH:MM:SS"),FILTER(ALLEXCEPT('Table','Table'[Emp ID]),
    'Table'[begin_date] <= MAX('Table'[begin_date])))