Calculate AVG value between certain dates

[CalvinL]

Hi all,

Looking to calculate the average of a value between certain dates. Below is a sample of the data that I have.

Date		Attribute	Value
01/03/2020 00:00:00		HH1	47
28/03/2020 00:00:00		HH1	58
15/03/2020 00:00:00		HH1	78
05/04/2021 00:00:30		HH2	66
21/04/2021 00:00:30		HH2	72

My intended result is to display the average value for each HH (half hour) for the entire month.

Example of the result:

Average of HH1 March		Average of HH2 April
61		69

Thank you.

[CalvinL]

Thanks guys, these solutions worked. Much appreciated!

[v-kelly-msft]

Hi  @CalvinL ,

You can create 2 calculated columns as below:

Month = FORMAT('Table'[Date.1],"MMMM")

Column = "Average of" &" "&'Table'[Attribute]&" "&'Table'[Month]

Then create a matrix visual,put column in the field of "column" ,value in the field of "value",right click on value,select "average":

And you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi @CalvinL , you can easily do this !!!
1. select  matrix visual.
2. put columns value to Value.
3. Select average from values i.e..

Hope You will get this....
Thanks

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution!

[parry2k]

@CalvinL you can easily achieve by following:

use matrix visual

put attribute on columns

value on values and choose aggregation to average

or create a measure

Avg = AVERAGE ( Table[Value] ) 

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[Pragati11 · Accepted solution]

Hi @CalvinL ,

Looking at your sample data, you can do the following:

• Create a table visual in Power BI
• Move ATTRIBUTE column to it.
• Move VALUE column to it with summarisation set to Average

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati