cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper I

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

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!

1 ACCEPTED SOLUTION
Super User

@Strongbuck OK, should be this then:

``````Avg Sound by Day Measure =
VAR __Table =
FILTER(
[__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).

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
6 REPLIES 6
Super User

@Strongbuck Maybe:

``````Avg Sound by Day Measure =
VAR __Table =
FILTER(
[__Hour] >= 6 && [__Hour] <= 20
)
VAR __Result = AVERAGEX(__Table, [Sound Levels])
RETURN
__Result``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper I

@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.

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper I

@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?

Community Support

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.

Super User

@Strongbuck OK, should be this then:

``````Avg Sound by Day Measure =
VAR __Table =
FILTER(
[__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).

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors