Averaging a Numeric Column and Filter the Data by Day, Hour and Minutes

[Strongbuck]

I need to average a numeric column and filter the data by day, hour and minutes.  I'm trying to display a chart that shows the sound levels data for workday hours.  The workday hours are 6:00 AM to 8:00 PM.

I've been able to accomplish this by day and hour but I haven't been able to isolate only data that is between the work hours because it needs to include the minutes.

I've tried to add a filter to an average forumla to isolate the hours.  I was able to add a column with only the hours data as numeric.  This let me code an equation but the data returned included hours after 8:00 PM (i.e. 8:10 PM, 8:30 PM, etc.)

There has to be a better way of doing this.  I tried searching for a solution that included hours and minutes but none of them included it.  This is the Sound_Level_History_All table:

Location	DateTime	Sound_Levels
Fitzrandolph	1/4/2021 0:00	40.3
Fitzrandolph	1/4/2021 0:10	40.1
Fitzrandolph	1/4/2021 0:15	40.1
Fitzrandolph	1/5/2021 0:20	40.4
Fitzrandolph	1/5/2021 0:25	40.5
Fitzrandolph	1/5/2021 15:30	40.7
Fitzrandolph	1/6/2021 0:35	41.1
Fitzrandolph	1/6/2021 0:40	40.9
Fitzrandolph	1/6/2021 0:45	40.8
Fitzrandolph	1/7/2021 0:50	40.8
Fitzrandolph	1/7/2021 15:40	40.9

This is the DAX for averaging by day.  I also tried to search for how to add the hour and minutes elements to the date hierachy so I could just replace the ".[Day] piece with it.

Avg Sound by Day =

AVERAGEX(

    KEEPFILTERS(VALUES('Sound_Level_History_All'[DateTime].[Day])),

    CALCULATE(AVERAGE('Sound_Level_History_All'[Sound_Levels]))

)

I've spent way to much time trying to figure this out myself.  Any help would be appreciated! 

[Greg_Deckler]

@Strongbuck Maybe:

Avg Sound by Day Measure =
  VAR __Table = 
    FILTER(
      ADDCOLUMNS( 'Sound_Level_History_All', "__Hour", HOUR([DateTime]) ),
      [__Hour] >= 6 && [__Hour] <= 20
    )
  VAR __Result = AVERAGEX(__Table, [Sound Levels])
RETURN
  __Result

[Strongbuck]

@Greg_Deckler I tried something similar but the issues is that the data is not capped at 8:00 PM.  It includes 8:10 PM, 8:20 PM, etc.

[_Hour] >=6 && [_Hour] <= 20

Sorry for the confusion.  I updated my original post.  I need the filter to include the hour and  minutes. 

[Greg_Deckler]

@Strongbuck So, [_Hour] >=6 && [_Hour] < 21 ?

[Strongbuck]

@Greg_Deckler One approach that might address the issue I am seeing, is rounding up the minutes.

My tests will be 8:00, 8:05, 8:15 and 8:30.  Only the record that has the 8:00 PM value should be included in the Average.  None of the others should be included.

Do you know the syntax on how to round the minutes up the the next hour if their value is anything but 00?

[v-xinruzhu-msft]

Hi @Strongbuck 

You can create a calculated column in table.

TimeValue = TIMEVALUE([DateTime])

Then create the measure.

Avg Sound by Day =
VAR _6 =
    TIMEVALUE ( "6:00" )
VAR _8 =
    TIME ( 20, 0, 0 )
RETURN
    CALCULATE (
        AVERAGE ( Sound_Level_History_All[Sound_Levels] ),
        FILTER ( Sound_Level_History_All, [TimeValue] >= _6 && [TimeValue] <= _8 )
    )

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler · Accepted solution]

@Strongbuck OK, should be this then:

Avg Sound by Day Measure = 
  VAR __Table = 
    FILTER(
      ADDCOLUMNS( 'Sound_Level_History_All', "__Hour", HOUR([DateTime]) ),
      [__Hour] >= 6 && ([DateTime] - INT([DateTime])) <= TIME(20, 0, 0)
    )
  VAR __Result = AVERAGEX(__Table, [Sound_Levels])
RETURN
  __Result

A datetime is a decimal value with the integer portion the data and the decimal portion the time. So subtracting the integer value from the datetime value leaves just the time which you can then compare to a time value created by the TIME function in which you specify a particular time (which in this case excludes minutes and seconds or sets them to zero anyway) which means that times like 16:10 (8:10 PM) are larger than 16:0:0 (8:00:00).