Calculate values allexcept the first value of each unique date formula help.

[tbobolz]

Hello all, I'm struggling with calculating an average of values, but needing it to ignore the very first value of each day as it is always zero. 

I'm averaging a total "Patient_Turnover_Time" field which list whole number values in minutes for each surgery performed. This is years’ worth of data; however, the first surgery of each day is always zero since theirs no previous surgery is preformed, so I need to ignore the first surgery of each day ("Scheduled_Start" Format example: 11/9/2018 9:00:00 AM) in this average.    

I have this average in a card visual. but the user could update the dashboard with filters.

I was playing around with this, but I can't seem to complete it:  

Average Turnover = CALCULATE(AVERAGE([PATIENT_TURNOVER_TIME]), ALLEXCEPT(Earliest(

Any help or suggestions would be appreciated.

Terry

[Greg_Deckler · Accepted solution]

@tbobolz Maybe:

Average Measure =
  VAR __Table = 'Table'
  VAR __IgnoreTable = SELECTCOLUMNS(SUMMARIZE('Table',[Date],"__Min",MIN([Scheduled_Start])),"__Scheduled_Start",[__Min])
  VAR __AverageTable = FILTER(__Table,NOT([Scheduled_Start] IN __IgnoreTable))
RETURN
  AVERAGEX(__AverageTable,[Patient_Turnover_Time])

[tbobolz]

@Greg_Deckler 

Thankyou very much, you are a genius!

Below is my formula with inserted fields; however, how would I add the condition to ingore that first scheduled surgery as you have written it, but only if it is equal to zero? 

Average Turnover =

VAR __Table = 'OR Turnover'

VAR __IgnoreTable = SELECTCOLUMNS(SUMMARIZE('OR Turnover',[SCHEDULED_START].[Date],"__Min",MIN([Scheduled_Start])),"__[Scheduled_Start]",[__Min])

VAR __AverageTable = FILTER(__Table,NOT([Scheduled_Start] IN __IgnoreTable))

RETURN

AVERAGEX(__AverageTable,[Patient_Turnover_Time])

[Greg_Deckler]

@tbobolz Try:

Average Turnover =
VAR __Table = 'OR Turnover'
VAR __IgnoreTable = SELECTCOLUMNS(SUMMARIZE(FILTER('OR Turnover',[Patient_Turnover_Time]=0),[SCHEDULED_START].[Date],"__Min",MIN([Scheduled_Start])),"__[Scheduled_Start]",[__Min])
VAR __AverageTable = FILTER(__Table,NOT([Scheduled_Start] IN __IgnoreTable))
RETURN
AVERAGEX(__AverageTable,[Patient_Turnover_Time])