Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
Strongbuck
Helper I
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:

 

LocationDateTimeSound_Levels
Fitzrandolph1/4/2021 0:0040.3
Fitzrandolph1/4/2021 0:1040.1
Fitzrandolph1/4/2021 0:1540.1
Fitzrandolph1/5/2021 0:2040.4
Fitzrandolph1/5/2021 0:2540.5
Fitzrandolph1/5/2021 15:3040.7
Fitzrandolph1/6/2021 0:3541.1
Fitzrandolph1/6/2021 0:4040.9
Fitzrandolph1/6/2021 0:4540.8
Fitzrandolph1/7/2021 0:5040.8
Fitzrandolph1/7/2021 15:4040.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

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

@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

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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

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

vxinruzhumsft_0-1708326706898.png

 

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.

 

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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