Forum Discussion

CalvinL's avatar
CalvinL
Helper II
6 years ago
Solved

Average over certain period

Hi all,

 

Looking to get an average of values between 00:00 to 12:00 for each day.

 

Sample dataset:

 

Date Value
12/02/2020 00:00:00 37 
12/02/2020 06:00:00 45
12/02/2020 12:00:00 32
13/03/2020 01:00:00 80
13/03/2020 06:00:00 95
13/03/2020 09:00:00 77

 

I want to display the values in a new column to be able to map it on a chart.

 

Any help appreciated, thanks!

Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    Hi CalvinL ,

     

    We can use the Power Query and create a calculate column to meet your requirement.

     

    1. In Power Query Editor, we can use Date only and Time only functions to create two columns.

     

     

    2. Then we can create a calculate columns in data view,

     

    Column = 
var time1 = TIME(0,0,0)
var time2 = TIME(12,0,0)
return
CALCULATE(AVERAGE('Table'[Value]),FILTER('Table',EARLIER('Table'[Date.1])='Table'[Date.1] && 'Table'[Time]>=time1&&'Table'[Time]<=time2))

     

     

     

    If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

    BTW, pbix as attached.

     

    Best regards,

     

    Community Support Team _ zhenbw

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

7 Replies

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Community Support
    6 years ago

    Hi CalvinL ,

     

    We can use the Power Query and create a calculate column to meet your requirement.

     

    1. In Power Query Editor, we can use Date only and Time only functions to create two columns.

     

     

    2. Then we can create a calculate columns in data view,

     

    Column = 
var time1 = TIME(0,0,0)
var time2 = TIME(12,0,0)
return
CALCULATE(AVERAGE('Table'[Value]),FILTER('Table',EARLIER('Table'[Date.1])='Table'[Date.1] && 'Table'[Time]>=time1&&'Table'[Time]<=time2))

     

     

     

    If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

    BTW, pbix as attached.

     

    Best regards,

     

    Community Support Team _ zhenbw

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

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      3 years ago

      I would like to take the average for a certain time interval, from 20215 11 months later and put that average on a graph as a reference line.

    • CalvinL's avatar
      CalvinL
      Helper II
      6 years ago

      Thanks for the reply. For the result, I want it to be displayed in a separate column, lets call it "Peak Values".  The reason I want it in a separate column is so that I can then see in a line chart that on this day, this was the peak value.

       

      Is that possible? I've given it a try in DAX but no joy. I know in excel it's quite easy with a BETWEEN statement, but unsure about DAX.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        HI CalvinL ,

         

        You can try this measure.

         

        AVG1 =
        var _year = YEAR(MAX('Table'[Date]))
        var _month = MONTH(MAX('Table'[Date]))
        var _day = DAY(MAX('Table'[Date]))
        var _filtertable = FILTER(ALL('Table') , Year('Table'[Date]) = _year && MONTH('Table'[Date]) = _month && DAY('Table'[Date]) = _day && HOUR('Table'[Date]) <= 12 && HOUR('Table'[Date]) >= 0)
        var _countrows = COUNTROWS(_filtertable)
        var _sum = CALCULATE(SUM('Table'[Value]),_filtertable)
        RETURN
        DIVIDE(_sum,_countrows)
         
         
         
         

        Regards,
        Harsh Nathani

        Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)